How to weekly group a data

Question

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

Tapan Patel · Answer

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 0Date)/7*70) [StartOfWeek] DATEADD( DAY DATEDIFF( DAY 0Date-1)/7*76) [EndOfWeek] Date Total FROM YOURTABLE ) AS CE GROUP BY CE.StartOfWeekce.EndOfWeek

Pravat Kumar · Answer

Hi Atul Please check this query it will solve your problem DECLARE @tblData TABLE(Date dateTimeTotal int) INSERT INTO @tblData(DateTotal) 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 WeekOfYearDateTotal from @tblData) A GROUP BY WeekOfYear

Atul Patil · Answer

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

Nitin Sontakke · Answer

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?

Ramesh Palanivel · Answer

Hi Atul Try this link It will help you to solve your query. http://stackoverflow.com/questions/25364654/sql-group-by-date-range