I have a table which has the following columns metric_id,sale_dt,rvnu_amt.
A metric can/cannot have data for the past 120 days.
Am trying to create a rolling metric rlp_rvnu_amt which will have the total sum of revenue for the past 120 days.
I tried using the sum(rvnu_amt) over(order by sale_dt rows 120 preceding) but it just sums the last 120 rows and not the 120 days as data is not available for some days in the past 120 days.
One approach is to convert your data to "dense" representation with rows for each day (perhaps "dummy" rows) and then summarize. Teradata offers the EXPAND ON clause (documented in the DML manual), or you can use something like "calendar_table LEFT JOIN metric_table" to generate rows for the "missing" dates.
How many many metrics & rows per metric_id exist?
Do you need a single Select or can you utilize a Volatile Table?