Need assistance with creating a moving sum for past 120 days

Teradata Applications

Need assistance with creating a moving sum for past 120 days

Hello,

 

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.

 

Thanks

Nick

 

3 REPLIES
Teradata Employee

Re: Need assistance with creating a moving sum for 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.

Re: Need assistance with creating a moving sum for past 120 days

Thank you Fred.I will check out the EXPAND ON clause.Will keep you posted.

 

Thanks

Nick

Junior Contributor

Re: Need assistance with creating a moving sum for past 120 days

How many many metrics & rows per metric_id exist?

Do you need a single Select or can you utilize a Volatile Table?