Week wise data from SQL.

Question

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 20-Jun-16 3:42am

Member 11494685

Updated 20-Jun-16 5:09am

Maciej Los

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2016-06-20T05:09:00

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