How to check on which DAY of each month and year is the highest value in some column in Teradata SQL-CodePudding

I have table in Teradata SQL like below:

col1        | col2
-----------------------
2021-01-22  | 123
2021-01-14  | 92
2021-04-05  | 444
2021-04-11  | 502
2020-03-11  | 89
2020-03-29  | 111
2020-05-28  | 7

And I would like to see on which DAY of each month and year is the highest value in "col2"

So as a result I need something like below (in col1 in below table is year and month):

col1    |  col2
------------
2021-01 | 123
2021-02 | 0
2021-03 | 0
2021-04 | 502
2021-05 | 0
2021-06 | 0
2021-07 | 0
2021-08 | 0
2021-09 | 0
2021-10 | 0
2021-11 | 0
2021-12 | 0
2020-01 | 0
2020-02 | 0
2020-03 | 111
2020-04 | 0
2020-05 | 7
2020-06 | 0
2020-07 | 0
2020-08 | 0
2020-09 | 0
2020-10 | 0
2020-11 | 0
2020-12 | 0

How to do that in Teradata SQL ?

CodePudding user response：

One option is to join to the calendar view to get your missing dates. This gives you the results you showed.

select
to_char(calendar_date,'YYYY-MM'),
max(col2)
from
sys_calendar.calendar c
left join vt_foo f
    on c.calendar_date = col1
where
c.year_of_calendar in (select distinct extract(year from col1) from vt_foo)
group by 1
order by 1

CodePudding user response：

This is a use case for Time Series Aggregation in TD16.20 :

SELECT To_Char(Last($Td_TimeCode_Range), 'YYYY-MM')
  ,Max(col2)
FROM vt
WHERE col1 BETWEEN DATE '2020-01-01' AND DATE '2021-12-31'
GROUP BY TIME(Cal_Months(1)) 
         USING  timecode(col1)
         FILL (0)