Sum Column for Running Total where Overlapping Date

Question

I have a table with about 3 million rows of Customer Sales by Date.

For each CustomerID row I need to get the sum of the Spend_Value WHERE Order_Date BETWEEN Order_Date_m365 AND Order_Date

Order_Date_m365 = OrderDate minus 365 days.

I just tried a self join but of course, this gave the wrong results due to rows overlapping dates.

If there is a way with Window Functions this would be ideal but I tried and can't do the between dates in the function, unless I missed a way.

Tonly way I can think now is to loop so process all rank 1 rows into a table, then rank 2 in a table, etc., but this will be really inefficient on 3 million rows.

Any ideas on how this is usually handled in SQL?

SELECT CustomerID,Order_Date_m365,Order_Date,Spend_Value
FROM   dbo.CustomerSales 

Answer 1

Window functions likely won't help you here, so you are going to need to reference the table again. I would suggest that you use an APPLY with a subquery to do this. Provided you have relevant indexes then this will likely be the more efficient approach:

SELECT CS.CustomerID,
       CS.Order_Date_m365,
       CS.Order_Date,
       CS.Spend_Value,
       o.output
FROM dbo.CustomerSales CS
     CROSS APPLY (SELECT SUM(Spend_Value) AS output
                  FROM dbo.CustomerSales ca
                  WHERE ca.CustomerID = CS.CustomerID
                    AND ca.Order_Date >= CS.Order_Date_m365 --Or should this is >?
                    AND ca.Order_Date <= CS.Order_Date) o;