How do I get the sum of a value for the past 21 days for every date in a table

Question

I would like to have the previous 21 days of sales for each day. For example it would take the sales from 03/01 to 03/21 and insert it into the line for day 03/21. Then for 03/22 it would shift the last 21 days to be 03/02 - 03/22. then 03/03 - 03/23. and so on.

This is what I currently have as a query.

SELECT '2021-03-21' AS 'Date', item, SUM(sales) 'Last 21 days Sales'
    FROM sales
WHERE item = 12345 AND date BETWEEN '2019-03-02' AND '2019-03-21'
GROUP BY item

Obviously this isn't close to what I need. But I am having trouble trying to visualize and make this into a possibility. Any help would be appreciated.

And you want every day for all of time to be presented, each row with its 21-day trailing sales sum? Or do you want the number of rows to be restricted in some way? What if there is a day with no sales, does it get a row? — Stuck at 1337, Nov 03 '22 at 18:15
Some sample data with desired results would be useful here. *Probably* an aggregate in a *cross apply* is what you're after. — Stu, Nov 03 '22 at 18:16
hey, maybe this can help. https://stackoverflow.com/questions/31852062/get-date-of-3-days-ago — Renato C.Francisco, Nov 03 '22 at 18:23
@RenatoC.Francisco I don't think so, they want a running/trailing sum for 21 days before each date, not a query that just pulls data from 21 days ago. — Stuck at 1337, Nov 03 '22 at 18:26

score 1 · Accepted Answer · answered Nov 03 '22 at 20:17

Here's an option for presenting demo data: Using this short snippet we can generate some random data to play with.

DECLARE @Sales TABLE (InvoiceID INT IDENTITY, SaleDateTime DATETIME, InvoiceAmount DECIMAL(10,3));
DECLARE @dt DATETIME, @invAmt DECIMAL(10,3);

WHILE (SELECT COUNT(*) FROM @Sales) < 500
BEGIN
SET @dt = DATEADD(DAY,(ROUND(((50 - 1 -1) * RAND() + 1), 0) - 1)*-1,CURRENT_TIMESTAMP) ;
SET @invAmt = (ROUND(((50000 - 1 -1) * RAND() + 1), 0) - 1)/100.0;
INSERT INTO @Sales (SaleDateTime, InvoiceAmount) 
SELECT @dt, @invAmt;
END

Now we have some sales data to work with. We've generated 500 rows with dates in the last 50 days, with an invoice amount of somewhere between 500.00 and 0.

We're gonna want to make two lots of data and stick them together - a daily total, and then the last 3 weeks of sliding totals as well. This is easily done by using CTEs:

;WITH InvoiceTotals AS (
SELECT CAST(SaleDateTime AS DATE) AS SalesDate, SUM(InvoiceAmount) AS TotalInvoices, COUNT(InvoiceAmount) AS CountInvoices
  FROM @Sales
 GROUP BY CAST(SaleDateTime AS DATE)
), ThreeWeeks AS (
SELECT a.SalesDate, SUM(it.TotalInvoices) AS ThreeWeekInvoices, SUM(it.CountInvoices) AS ThreeWeekCountInvoices
  FROM (
        SELECT SalesDate
          FROM InvoiceTotals
         GROUP BY SalesDate
       ) a
    INNER JOIN InvoiceTotals it
      ON it.SalesDate BETWEEN DATEADD(DAY,-21,a.SalesDate) AND a.SalesDate
 GROUP BY a.SalesDate
)
SELECT it.SalesDate, it.TotalInvoices, it.CountInvoices, tw.ThreeWeekInvoices, tw.ThreeWeekCountInvoices
  FROM InvoiceTotals it
    INNER JOIN ThreeWeeks tw
      ON it.SalesDate = tw.SalesDate
 ORDER BY it.SalesDate;

SalesDate   TotalInvoices   CountInvoices   ThreeWeekInvoices   ThreeWeekCountInvoices
--------------------------------------------------------------------------------------
2022-09-16  2076.200        9               2076.200            9
2022-09-17  2786.980        10              4863.180            19
2022-09-18  3361.460        13              8224.640            32
2022-09-19  2566.090        9               10790.730           41
...
2022-10-30  3215.190        10              58833.890           224
2022-10-31  2922.720        10              59682.320           226
2022-11-01  1009.560        5               57281.430           217
2022-11-02  2080.830        7               56985.850           216
2022-11-03  890.710         4               56904.820           214

Obviously there isn't enough data for the first few days to have 21 days of history, so those are smaller amounts/counts.

How do I get the sum of a value for the past 21 days for every date in a table

1 Answers1