CODEWITHSUNDEEP
sqldateteradatapartitioningwindow-functions
Maya
Maya

Reputation: 11

How to find the maximum value in a range of following rows - SQL Teradata

I have a table with the following colums:

account, validity_date, validity_month, amount

For each row I want to find the maximum amount over the rows range of the next month.

account   validity_date   validity_month   amount    **required_column**
-------   -------------   ---------------  -------   ----------------
123        15oct2019       201910           400         1000
123        20oct2019       201910           500         1000
123        15nov2019       201911           1000        800
123        20nov2019       201911           0           800
123        15dec2019       201912           800

When I tried this code, I get the value in the next row but that's not what I need (there can be more than one row in each month):

MAX(amount) OVER (PARTITION BY account ORDER BY validity_date 
                  ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_max_amount

account   validity_date   validity_month   amount    next_max_amount-INVALID
-------   -------------   ---------------  -------   ----------------------
123        15oct2019       201910           400         500
123        20oct2019       201910           500         1000
123        15nov2019       201911           1000        0
123        20nov2019       201911           0           800
123        15dec2019       201912           800

Upvotes: 1

Views: 799

Answers (2)

access_granted
access_granted

Reputation: 1907

Assuming the validity_month is a date type column:

select s1.account, s1.validity_date, s1.validity_month, s1.amount, 
  s2.amount required_column
from your_table s1
left join (select max(amount) amount, validity_month from your_table group by 2) s2
on (
  to_char(s1.validity_month.'yyyymm')=to_char(add_month(s2.validity_month,1),'yyyymm')
)

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

SELECT dt.*,
   -- fill the NULLs with the previous month's max
   Last_Value(CASE WHEN rn = 1 THEN current_max END IGNORE NULLS)
   Over (PARTITION BY account
         ORDER BY validity_date DESC
         ROWS BETWEEN Unbounded Preceding AND 1 Preceding)
FROM
 (
   SELECT t.*,
      -- maximum of current month
      Max(amount)
      Over (PARTITION BY account, validity_month) AS current_max,
      -- to identify the 1st row for each month in the next step
      Row_Number()
      Over (PARTITION BY account, validity_month
            ORDER BY validity_date) AS rn      
   FROM tab AS t
 ) AS dt

Upvotes: 2

Related Questions