userNid - 1 year ago 125

SQL Question

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 Source

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
```