question

SSGC avatar image
SSGC asked

How to get 14 days total for each 7 days?

Hi, Professional, can Someone help me for this case? I like to get a total sales for posted 14 days for each day of last week. For Example, last week we have: 06/20,06/21,06/22,06/23,06/24,06/25,06/26. For 06/20, I need the total for 06/06 - 06/19, For 06/21, I need the total for 06/07 - 06/20,... For 06/26, I need the total for 06/12 - 06/25. For yesterday I may can use: SELECT Product, SUM(Sale_Amount) FROM Sale WHERE DATEDIFF(DAY,Sale_Date,DATEADD(day, -1, GETDATE())) <= 14 GROUP BY Product does it possible have a query to get 14 days total for last 7 days? Thanks!
t-sqlgroup-byrunning-totaltotal
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs by votes. For all the helpful answers below, please indicate this by clicking on the thumbs up next to each of those answers. If any one answer lead to a solution, please indicate this by clicking the check mark next to that answer.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
I tested this on my own data and then changed the columns to match your code in the question. Please try it and let me know how well it works for you. DECLARE @EffectiveDate date = SYSDATETIME(); WITH DailyStats AS ( SELECT s.Sale_Date , DailyTotal = SUM(s.Sale_Amount) FROM Sale s WHERE s.Sale_Date BETWEEN DATEADD(day, -20, CONVERT(date, @EffectiveDate)) AND @EffectiveDate GROUP BY s.Sale_Date ) , RunningTotals AS ( SELECT ds.Sale_Date , ds.DailyTotal , Past14Days = SUM(ds.DailyTotal) OVER (ORDER BY ds.Sale_Date ROWS 13 PRECEDING) FROM DailyStats ds ) SELECT rt.Sale_Date , rt.DailyTotal , rt.Past14Days FROM RunningTotals rt WHERE rt.Sale_Date >= DATEADD(day, -6, @EffectiveDate) ORDER BY rt.Sale_Date DESC ;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Tom, Thank you very much for your help! The windows function just work great. A little issues for me is the Sale_date is not continuously. The Rows 13 will keep catch 14 days sales but not last 14 days. I mean if sales missing on sunday and saterday, The running total will not exactly for last 14 days. For Example, for 06/26, I need the total for 06/12 - 06/25, if 06/13, 06/14 and 06/0, 06/21 do not have sales at all. The total will summary for 06/08-06/25. Is there a function can only count last 14 days not 14 rows? Best Regards!
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could use a Numbers, or Tally, table to generate consecutive dates, and left join to them. This worked on my test data: WITH Numbers1(nr) AS( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),Numbers(n) AS( SELECT ROW_NUMBER() OVER(ORDER BY n1.nr) FROM Numbers1 n1 CROSS APPLY Numbers1 n2 ),Dates (dt) AS( SELECT CAST(DATEADD(day,-1*n,CURRENT_TIMESTAMP) as date) FROM Numbers ),SalesPerDay(dt,TotalSales) AS( SELECT d.dt as Sale_Date, COALESCE(SUM(s.Sale_Amount),0) FROM Dates d LEFT JOIN dbo.Sale s ON d.dt = s.Sale_Date GROUP BY d.dt ),RunningTotals(Sale_Date,SalesForDay,SalesPast14Days) AS( SELECT dt, TotalSales, SUM(TotalSales) OVER(ORDER BY dt ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) FROM SalesPerDay )SELECT top 7 * FROM RunningTotals ORDER BY Sale_Date DESC;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.