December 23, 2016 at 2:21 am
I want to get sum of any last 7 days periods of month.
Date Total rest of day
1-12-2016 10
2-12-2016 12
3-12-2016 10
4-12-2016 11
5-12-2016 12
6-12-2016 10
7-12-2016 11 76 (total of 7 days 1-7)
8-12-2016 10 76(total of 7 days 2-8)
9-12-2016 9 73(total of 7 days 3-9)
10-12-2016 10 73(total of 7 days 4-10)
11-12-2016 13 75(total of 7 days 5-11)
And so on.
December 23, 2016 at 2:40 am
You've posted in the SQL Server 2008 forum, so I assume that's what you're using. If so, search this site (or elsewhere) for Running Totals. If you're on SQL Server 2012 or higher, it's a lot simpler - you can use the windowing functions, something like this:SUM(Total) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
That's from memory, so please check the syntax and, of course, that it actually gives the desired results.
Edit: this assumes that you have a row (and only one row) for each date. If that's not the case then adding up the last seven rows is clearly not going to give the total for the last seven days.
John
December 23, 2016 at 3:47 am
saklanivijay87 (12/23/2016)
I want to get sum of any last 7 days periods of month.Date Total rest of day
1-12-2016 10
2-12-2016 12
3-12-2016 10
4-12-2016 11
5-12-2016 12
6-12-2016 10
7-12-2016 11 76 (total of 7 days 1-7)
8-12-2016 10 76(total of 7 days 2-8)
9-12-2016 9 73(total of 7 days 3-9)
10-12-2016 10 73(total of 7 days 4-10)
11-12-2016 13 75(total of 7 days 5-11)
And so on.
Here's one possible solution
/***********************************************
Create sample data
************************************************/
DECLARE @table TABLE
(
EventDateDATE
,TotalINT
)
INSERT INTO @table VALUES
('2016-12-01',10)
,('2016-12-02',12)
,('2016-12-03',10)
,('2016-12-04',11)
,('2016-12-05',12)
,('2016-12-06',10)
,('2016-12-07',11)
,('2016-12-08',10)
,('2016-12-09',9)
,('2016-12-10',10)
,('2016-12-11',13);
/****************************************************
Solution start
*****************************************************/
SELECT
t.EventDate
,t.Total
,SUM(t2.total)
FROM
@table t
CROSS APPLY
(
SELECT
*
FROM @table t1
WHERE
t1.EventDate > DATEADD(DAY,-7,t.EventDate)
AND t1.EventDate < DATEADD(DAY,1,t.EventDate)
)t2
GROUP BY
t.EventDate
,t.Total
ORDER BY
t.EventDate
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply