Question about Aggregate function OVER syntax

Database

Question about Aggregate function OVER syntax

Can someone help me with a solution in teradata SQL

here is the source data

 

ID, YearWeek, amount

1234, 201744, 0

1234, 201745, 0

1234, 201746, 10

1234, 201747, 0

1234, 201748, 0

1234, 201749, 200

1234, 201750, 0

1234, 201751, 45

1234, 201752, 0

1234, 201753, 0

1234, 201801, 0

1234, 201802, 244

1234, 201803, 0

1234, 201804, 14

 

 

The result should look like 

 

ID, YearWeek, amount, Amountnew

1234, 201744, 0, 0

1234, 201745, 0, 0

1234, 201746, 10, 10

1234, 201747, 0, 10

1234, 201748, 0, 10

1234, 201749, 200, 200

1234, 201750, 0, 200

1234, 201751, 45, 45

1234, 201752, 0, 45

1234, 201753, 0, 45

1234, 201801, 0, 45

1234, 201802, 244, 244

1234, 201803, 0, 244

1234, 201804, 14, 14

 

As you can see, I am looking for SQL that will help me carry over the previous value of Amount to the next weeks when the week does not have a value.

 

It should reset to a new value when a week reports an amount other than 0 to a new amount to be continued further.

 

How can I use some aggregate function like max OVER partition by and may be some Qualify syntax or RESET WHEN syntx to get this effect ?

 

Thanks in Advance

 


Accepted Solutions

Re: Question about Aggregate function OVER syntax

Thanks Rohan for your help. That worked just fine. cheers.

1 ACCEPTED SOLUTION
3 REPLIES
Supporter

Re: Question about Aggregate function OVER syntax

Hi Joeyfoxx,

 

The below query must work for you.

SELECT
  ID
, YEARWEEK
, AMOUNT
, MAX(AMOUNT) OVER 
  (
    PARTITION BY ID ORDER BY YEARWEEK 
      RESET WHEN 
      (AMOUNT< (MAX(AMOUNT) OVER (PARTITION BY ID ORDER BY YEARWEEK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))AND AMOUNT<>0)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS NEWAMOUNT
FROM
  YOUR_TABLE
ORDER BY ID, YEARWEEK;

Hope it helps.

 

Thanks,

Rohan Sawant

Junior Contributor

Re: Question about Aggregate function OVER syntax

Since TD14.10 there's LAST_VALUE, which has this nice IGNORE NULLS option:

LAST_VALUE(NULLIF(amount, 0) IGNORE NULLS)
OVER (PARTITION BY ID
      ORDER BY YearWeek)

To get the first two rows with zeroes you might wrap it in a COALESCE.

Re: Question about Aggregate function OVER syntax

Thanks Rohan for your help. That worked just fine. cheers.