calculating each day sum and last 7 days sum for max date to last 7 days and 14 days and 21 days sum in new columns

Question

I have the following data set: which is daily sum. Based on this, how do i get last 7 days sum, 14 days sum and 21 days for each row in new columns.

I have tried to get last 7 days sum by using below query. but Its is giving cumulative sum instead of giving lookback to last 7 days.

select date,sum(amount) as amount,
   sum(sum(amount)) over (order by date desc rows between 6 preceding and current row) as amount_7days_ago from table1 group by date order by date desc;

from there on, not able to move forward with the idea. how can i achieve this in query?

Desired Result :

Answer 1

If I understand correctly you can try to use self-join instead of window function.

;WITH CTE AS (
   SELECT [DATE],
          SUM(amount) as amount
   FROM table1
   GROUP BY [DATE]
)
SELECT t2.[DATE],
       t2.amount as amount,
       sum(CASE WHEN t1.Date >= DATEADD(DAY,  -7, t2.[Date]) AND t1.Date < t2.Date THEN t1.amount ELSE 0 END)  as amount_7days_ago,
       sum(CASE WHEN t1.Date >= DATEADD(DAY,  -14, t2.[Date]) AND t1.Date < t2.Date THEN t1.amount ELSE 0 END) as amount_14days_ago,    
       Sum(CASE WHEN t1.Date >= DATEADD(DAY,  -21, t2.[Date]) AND t1.Date < t2.Date THEN t1.amount ELSE 0 END) as amount_14days_ago
FROM CTE t1
INNER JOIN CTE t2
ON t1.[DATE] <= t2.[Date]
GROUP BY t2.[DATE],t2.amount
ORDER BY t2.[DATE] DESC

sqlfiddle

Answer 2

If I read your question correctly - you want a rolling 7 days total, rolling 14 days and rolling 21 days.

If that is correct, then the problem you have is adding the group by and attempting to sum the windowing function. And - you have the window incorrectly defined:

   With sampleData
     As (
 Select SampleDate      = dateadd(day, -row_number() Over(Order By @@spid) + 1, cast(getdate() As date))
      , SampleAmount    = cast(row_number() Over(Order By @@spid) * 1000 As money)
   From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))       As t1(n)
  Cross Apply (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t2(n)
        )
      , summaryData
     As (
 Select *
      , Last7Days       = sum(sd.SampleAmount) Over(Order By sd.SampleDate desc
                                                        Rows Between Current Row And 6 Following)
      , Last14Days      = sum(sd.SampleAmount) Over(Order By sd.SampleDate desc
                                                        Rows Between Current Row And 13 Following)
      , Last21Days      = sum(sd.SampleAmount) Over(Order By sd.SampleDate desc
                                                        Rows Between Current Row And 20 Following)
   From sampleData                      sd
        )
 Select *
   From summaryData
  Where SampleDate >= eomonth(getdate(), -2);

We can order by the sample date descending - if we do that then we need a window from the current row to the 6/13/20 following rows. Or - we switch to ascending and pull the 6 preceding rows to current row:

   With sampleData
     As (
 Select SampleDate      = dateadd(day, -row_number() Over(Order By @@spid) + 1, cast(getdate() As date))
      , SampleAmount    = cast(row_number() Over(Order By @@spid) * 1000 As money)
   From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))       As t1(n)
  Cross Apply (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t2(n)
        )
      , summaryData
     As (
 Select *
      , Last7Days       = sum(sd.SampleAmount) Over(Order By sd.SampleDate
                                                        Rows Between 6 Preceding And Current Row)
      , Last14Days      = sum(sd.SampleAmount) Over(Order By sd.SampleDate
                                                        Rows Between 13 Preceding And Current Row)
      , Last21Days      = sum(sd.SampleAmount) Over(Order By sd.SampleDate
                                                        Rows Between 20 Preceding And Current Row)
   From sampleData                      sd
        )
 Select *
   From summaryData
  Where SampleDate >= eomonth(getdate(), -2);

Note: either way you look at this, you need to pull at least 21 days of data prior to the beginning of the range you want to display. For example, if you want to show from current to beginning of this month you need to include at least 21 days in the summaryData:

Declare @startDate date = '2022-05-01';

   With sampleData
     As (
 Select SampleDate      = dateadd(day, -row_number() Over(Order By @@spid) + 1, cast(getdate() As date))
      , SampleAmount    = cast(row_number() Over(Order By @@spid) * 1000 As money)
   From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))       As t1(n)
  Cross Apply (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t2(n)
        )
      , summaryData
     As (
 Select *
      , Last7Days       = sum(sd.SampleAmount) Over(Order By sd.SampleDate
                                                        Rows Between 6 Preceding And Current Row)
      , Last14Days      = sum(sd.SampleAmount) Over(Order By sd.SampleDate
                                                        Rows Between 13 Preceding And Current Row)
      , Last21Days      = sum(sd.SampleAmount) Over(Order By sd.SampleDate
                                                        Rows Between 20 Preceding And Current Row)
   From sampleData                      sd
  Where sd.SampleDate >= dateadd(day, -21, @startDate)
        )
 Select *
   From summaryData
  Where SampleDate >= @startDate;

Answer 3

I Got the answer for my self. Thanks for your help.

    ;WITH CTE AS (
   SELECT [DATE],
          SUM(amount) as amount
   FROM table1
   GROUP BY [DATE]
)
SELECT t2.[DATE],
       t2.amount as amount,
       sum(CASE WHEN t1.Date = DATEADD(DAY,  -7, t2.[Date]) AND t1.Date < t2.Date THEN t1.amount ELSE 0 END)  as amount_7days_ago,
       sum(CASE WHEN t1.Date = DATEADD(DAY,  -14, t2.[Date]) AND t1.Date < t2.Date THEN t1.amount ELSE 0 END) as amount_14days_ago,    
       Sum(CASE WHEN t1.Date = DATEADD(DAY,  -21, t2.[Date]) AND t1.Date < t2.Date THEN t1.amount ELSE 0 END) as amount_14days_ago
FROM CTE t1
INNER JOIN CTE t2
ON t1.[DATE] <= t2.[Date]
GROUP BY t2.[DATE],t2.amount
ORDER BY t2.[DATE] DESC