I want to find the average of item price bought within the last 365 days. Items are not guaranteed to be bought every day, so I can't fix the number of rows to look back at. So I am trying to use RANGE instead of ROWS, specifying that I look back 365 days from current row's date.
Sample data:
Store | Item | Date bought | Price | Avg price across last 365 days |
---|---|---|---|---|
Store 1 | Item 1 | 1/2/2022 | 1.00 | 1.00 |
Store 1 | Item 1 | 6/1/2022 | 1.75 | 1.375 |
Store 1 | Item 1 | 11/2/2022 | 2.10 | 1.617 |
Store 1 | Item 1 | 1/5/2023 | 3.00 | 2.283 |
Store 2 | Item 1 | 3/2/2022 | 1.55 | 1.55 |
Store 2 | Item 1 | 5/5/2022 | 2.80 | 2.175 |
I have tried:
SELECT
store, item, date, price,
SUM(price) OVER (PARTITION BY store, item
ORDER BY date ASC
RANGE BETWEEN 365 DAY PRECEDING AND CURRENT ROW) AS avg_price
FROM table
Error I get is:
Msg 102, Level 15, State 1, Line 102
Incorrect syntax near 'DAY'
I have tried these variations to address the error but can't get past it:
RANGE BETWEEN '365' DAY PRECEDING AND CURRENT ROW
RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND CURRENT ROW
RANGE BETWEEN 365 PRECEDING AND CURRENT ROW
#3 produces the error
Msg 4194, Level 16, State 1, Line 98
RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.
Is this a syntax error? I am using Microsoft SQL Server Management Studio.
A good old self-join should work (I converted your dates into ISO format):
with cte as (
select *
from (
VALUES (N'Store 1', N'Item 1', N'2022-01-02', 1.00, 1.00)
, (N'Store 1', N'Item 1', N'2022-06-01', 1.75, 1.375)
, (N'Store 1', N'Item 1', N'2022-11-01', 2.10, 1.617)
, (N'Store 1', N'Item 1', N'2023-01-05', 3.00, 2.283)
, (N'Store 2', N'Item 1', N'2022-03-02', 1.55, 1.55)
, (N'Store 2', N'Item 1', N'2022-05-05', 2.80, 2.175)
) t (Store,Item,[Date bought],Price,[Avg price across last 365 days])
)
select AVG(c2.price), c.Store, c.Item, c.[Date bought]
from CTE c
LEFT JOIN CTE c2
On c2.Store = c.Store
AND c2.Item = c.Item
AND c2.[Date bought] between DATEADD(YEAR, -1,CAST(c.[Date bought] AS DATETIME)) AND c.[Date bought]
GROUP BY c.Store, c.Item, c.[Date bought]