CODEWITHSUNDEEP
sqlteradatawindow-functions
Ahmed Mohammed Abdel Kader
Ahmed Mohammed Abdel Kader

Reputation: 149

Get the rolling Last_Date for a transactions SQL Teradata

Please I have a simple table with two columns like the below

|Connect_Date|TRX_Count|
|------------+---------|
|1-May       |         |
|2-May       |         |
|3-May       |        3|
|4-May       |         |
|5-May       |         |
|6-May       |        4|
|7-May       |         |
|8-May       |        7|
|9-May       |         |
|10-May      |       10|

And I need to insert a new column that has the last Date of being TRX_Count is greater than 0 or null, and the result will be like the below

|Connect_Date|TRX_Count|Last_Date|
|------------+---------+---------|
|1-May       |         |3-May    |
|2-May       |         |3-May    |
|3-May       |        3|3-May    |
|4-May       |         |6-May    |
|5-May       |         |6-May    |
|6-May       |        4|6-May    |
|7-May       |         |8-May    |
|8-May       |        7|8-May    |
|9-May       |         |10-May   |
|10-May      |       10|10-May   |

Upvotes: 0

Views: 104

Answers (2)

dnoeth
dnoeth

Reputation: 60462

This fills NULLs at the begin and the end:

coalesce(min(case when TRX_Count is not null then Connect_Date end)
         over (order by Connect_Date desc
               rows unbounded preceding)
        ,max(case when TRX_Count is not null then Connect_Date end) over ()
        )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269653

Use a cumulative minimum:

select t.*,
       min(case when trx_count is not null then connect_date end) over (order by connect_date rows between current row and unbounded following) as last_date
from t;

Or lead(ignore nulls):

select t.*
       lead(case when trx_count is not null then connect_date end ignore nulls) over (order by connect_date)
from t;

Upvotes: 2

Related Questions