Click here to Skip to main content
15,034,598 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have data like

date		count
-----------------------
2016-01-01 	1
2016-01-02	1
2016-01-03	2
2016-01-04	1
2016-01-05	3
2016-01-06	4
2016-01-07	2
2016-01-08	5
2016-01-09	1
2016-01-10	2
2016-01-11	2
2016-01-12	1
2016-01-13	3


i want week wise data and for 1st week i need 1st week data, for 2nd week i need 1st week + 2nd week and for 3rd week i need 1st week + 2nd week + 3rd week data

week wise (Monday to sunday)

I need output as below:
date 	 |  2016-01-01	|    2016-01-04	  |  2016-01-11	
count	  | 4 |	22 |	28
Posted
Updated 20-Jun-16 5:09am
v3
Comments
OriginalGriff 20-Jun-16 9:44am
   
And?
What have you tried?
Where are you stuck?
What help do you need?
CHill60 20-Jun-16 10:08am
   
Your statement of requirements does not match the expected output - the words say you want cumulative sums but the output is showing the sum of count for each week
Member 11494685 20-Jun-16 10:34am
   
1st week of jan is 2016-01-01 to 2016-01-03 -- sum = 4.
In same way 2nd week is 2016-01-04 to 2016-01-10 -- here i need 1st week data and 2nd week data sum = 22
CHill60 20-Jun-16 10:38am
   
And you want it on a single row? I.e.
2016-01-01 | 2016-01-04 | 2016-01-11
4 | 22 | 28
or do you want it like
2016-01-01 | 4
2016-01-04 | 22
2016-01-11 | 28
Maciej Los 20-Jun-16 11:19am
   
To be honest, a second result set is necessary to get the first one. Sorry for my enigmatic explanation ;)
Please, see my answer, Caroline.
CHill60 20-Jun-16 11:24am
   
Yes - I know what you mean :-) I was trying to get out of the OP whether or not that pivot was required.
Member 11494685 21-Jun-16 0:52am
   
Thank You.
Maciej Los 21-Jun-16 3:04am
   
You're very welcome.
Member 11494685 22-Jun-16 4:20am
   
can u please answer to this.

http://www.codeproject.com/Questions/1108139/Hi-I-need-to-get-group-by-week-data-If-data-not-pr

1 solution

All what you need to do is to create query called "running sum", "running totals", etc.

Check these:
Calculating simple running totals in SQL Server[^]
SQL SERVER - How to Find Running Total in SQL Server - Journey to SQL Authority with Pinal Dave[^]

Then, you have to pivot data. That's all!

Try:
SQL
DECLARE @tmp TABLE(adate DATE, acount INT)

INSERT INTO @tmp(adate, acount)
VALUES('2016-01-01', 1),
('2016-01-02', 1),
('2016-01-03', 2),
('2016-01-04', 1),
('2016-01-05', 3),
('2016-01-06', 4),
('2016-01-07', 2),
('2016-01-08', 5),
('2016-01-09', 1),
('2016-01-10', 2),
('2016-01-11', 2),
('2016-01-12', 1),
('2016-01-13', 3)

SET DATEFIRST 1;
SELECT [1], [2], [3]
FROM (
	SELECT a.weekno, 
			(SELECT SUM(acount) AS runningsum
			FROM @tmp AS b
			WHERE DATEPART(wk, adate) <= a.weekno) AS RunningSum
	FROM (
		SELECT adate, acount, DATEPART(wk, adate) AS weekno
		FROM @tmp 
		) AS a
	GROUP BY a.weekno
) AS DT
PIVOT (SUM(runningsum) FOR weekno IN ([1], [2], [3])) AS pt 


Result:
1	2	3
4	22	28
   
v2
Comments
[no name] 20-Jun-16 11:15am
   
Too much for my small brain. A 5. Bruno
Maciej Los 20-Jun-16 11:16am
   
Thank you, Bruno. Don't be so critical to yourself. You're very smart person.
Cheers, Maciej
jaket-cp 20-Jun-16 11:30am
   
genius +5
but not sure what SET DATEFIRST 1; is for :)
Maciej Los 20-Jun-16 13:39pm
   
Comment that line using -- (double hyphen) to find out what happens ;)
On the other side, check MSDN documentation.
Thank you.
CHill60 20-Jun-16 14:11pm
   
Maciej Los 20-Jun-16 14:22pm
   
Thank you, Caroline. And thank you for reminding me that great group.
My point of view about mondays: every monday reminds me that another week has just began...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900