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 :
CodePudding user response:
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
CodePudding user response:
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;