How to weekly group a data

Atul Patil

9y1k

I'm currently stuck with this problem. Suppose I have the following daily data

+-------------------------------+----------

| Date | Total |

+-------------------------------+----------

| 2016-08-01 00:00:00.000 | 10 |

| 2016-08-02 00:00:00.000 | 10 |

| 2016-08-03 00:00:00.000 | 20 |

| 2016-08-04 00:00:00.000 | 40 |

| 2016-08-05 00:00:00.000 | 20 |

| 2016-08-06 00:00:00.000 | 10 |

| 2016-08-07 00:00:00.000 | 50 |

| 2016-08-16 00:00:00.000 | 57 |

| 2016-08-17 00:00:00.000 | 52 |

+-------------------------+-----+----------

How will I group it in such a way that it will sum Total by weekly basis? Expected output should be

2012-08-01 to 2012-08-07 = 160

2012-08-08 to 2012-08-15=0

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
You can use this query to go through any number of data grouped by their start of the week and end of week and summed over Total column.
SELECT CONVERT(VARCHAR(200),CE.StartOfWeek)+' - '+CONVERT(varchar(200),CE.EndOfWeek) [WeekRange]
       ,MIN(CE.Date),
       MAX(CE.Date) ,
       SUM(CE.Total )
FROM
(

SELECT DATEADD(DAY,DATEDIFF(DAY,0,Date)/7*7,0) [StartOfWeek],
        DATEADD(DAY,DATEDIFF(DAY,0,Date-1)/7*7,6) [EndOfWeek],
Date,
Total
FROM YOURTABLE

) AS CE
GROUP BY CE.StartOfWeek,ce.EndOfWeek
0
Pravat Kumar9y ago
Hi Atul,
Please check this query, it will solve your problem

DECLARE @tblData TABLE(Date dateTime,Total int)
INSERT INTO @tblData(Date,Total)
SELECT '03/9/2016',19
UNION SELECT '03/22/2016',10
UNION SELECT '03/23/2016',15
UNION SELECT '03/24/2016',10
UNION SELECT '03/25/2016',15
UNION SELECT '01/26/2016',19
UNION SELECT '03/27/2016',10
UNION SELECT '03/28/2016',15
UNION SELECT '03/29/2016',10
UNION SELECT '03/30/2016',5
SELECT CONVERT(VARCHAR(10),MIN(Date),126) + ' - ' + CONVERT(VARCHAR(10),MAX(Date),126)
,SUM(Total) AS Total
FROM
(SELECT DATEPART(WEEK, Date) AS WeekOfYear,Date,Total from @tblData) A
GROUP BY WeekOfYear
+1
Atul Patil9y ago
sorry it's my mistake, want result like this and starting day is monday

2016-08-01 to 2016-08-07 = 160
2016-08-08 to 2016-08-15=0

0
Nitin Sontakke9y ago
In data, you mention year 2016 and in output you change that to 2012? Is there a typo.

Please also mention what is the basis of starting a week from 1, is it the 1 of the month to 7th of the month = 1st week?

And do you want this report of 1 month only?
0
Ramesh Palanivel9y ago
Hi Atul,

Try this link,

It will help you to solve your query.

http://stackoverflow.com/questions/25364654/sql-group-by-date-range
0