Search code examples
sqlrangeteradatamonthcalendar

First month customers amount

Teradta - SQL I need to show (new_col) the amount in all months only to clients of the first month

have:

date        cust sum
2020-04-01  1    100
2020-04-01  2    100
2020-05-01  1    100
2020-05-01  3    100
2020-06-01  4    100
2020-06-01  2    100

Need:

date        cust sum  new_col
2020-04-01  1    100  100
2020-04-01  2    110  110
2020-05-01  1    120  120
2020-05-01  3    100  null
2020-06-01  4    100  null
2020-06-01  2    140  140
Solution

  • Seems you need a Group Min.

    Assuming the date is always the 1st of month:

    case
  when min(date) over (partition by cust) -- min date for customer
     = min(date) over ()                  -- min date overall
  then sum
end