userNid userNid - 5 months ago 24
SQL Question

Teradata SQL moving sum

I have a table of 2 columns which has daily sales by date. I need to calculate a column which sums sales for the last 365 days.

I think we could use SUM Over with ROW Preceeding option. However I am not sure of the syntax.

Please help.

RPT_DT sales last_year_sales
2/1/2015 150,876.00
.
.
.
1/29/2016 430,715.31
1/30/2016 407,864.88
1/31/2016 355,793.55
2/1/2016 331,142.13 SUM sales from 2/1/15 to 01/31/16
2/2/2016 269,016.02 SUM sales from 2/2/15 to 02/01/16
2/3/2016 295,007.69 SUM sales from 2/3/15 to 02/02/16

Answer

try this. Adjust "preceding" cutoffs per your need.hope this helps.

    Select 
    rpt_dt,
    sales,
    SUM(sales) OVER (ORDER BY rpt_dt ROWS BETWEEN 2 PRECEDING AND  1 PRECEDING)
    FROM test
    GROUP BY 1,2
    ORDER BY 1
Comments