How weekly if you have daily data

Question

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

+-------------------------+-----+----+ | Date | C1 | C2 | +-------------------------+-----+----+ | 2012-08-01 00:00:00.000 | 44 | 44 | | 2012-08-02 00:00:00.000 | 51 | 49 | | 2012-08-03 00:00:00.000 | 60 | 59 | | 2012-08-04 00:00:00.000 | 68 | 67 | | 2012-08-05 00:00:00.000 | 82 | 78 | | 2012-08-06 00:00:00.000 | 62 | 59 | | 2012-08-07 00:00:00.000 | 58 | 53 | | 2012-08-08 00:00:00.000 | 69 | 65 | | 2012-08-09 00:00:00.000 | 82 | 72 | | 2012-08-10 00:00:00.000 | 70 | 68 | | 2012-08-11 00:00:00.000 | 75 | 71 | | 2012-08-12 00:00:00.000 | 64 | 64 | | 2012-08-13 00:00:00.000 | 74 | 69 | | 2012-08-14 00:00:00.000 | 60 | 56 | | 2012-08-15 00:00:00.000 | 66 | 60 | | 2012-08-16 00:00:00.000 | 57 | 51 | | 2012-08-17 00:00:00.000 | 52 | 49 | +-------------------------+-----+----+ 

How will I group it so that it sums C1 and C2 weekly? Expected result should be

 +---------------------------+------+----+ | Date | C1 | C2 | +---------------------------+------+----+ | 2012-08-06 to 2012-12-12 | 480 | 452| | 2012-08-13 to 2012-08-19 | 430 | 394| +---------------------------+------+----+ 

It started from 2012-08-06, as the cycle should be from Monday to Sunday. I tried to walk for about an hour or so, and it seems that no result is consistent with my problem, I hope someone can help me.

Thanks!

Answer 1

try the following:

SET DATEFIRST 1 sets your week start on Monday

 SET DATEFIRST 1 SELECT CAST(MIN( [DATE]) AS VARCHAR(20))+' TO '+CAST (MAX([DATE]) AS VARCHAR(20)) AS DATE, SUM(C1) AS GRU, SUM(C2) AS C1 FROM YOUR_TABLE GROUP BY DATEPART(WEEK,[DATE]) HAVING COUNT(DISTINCT[DATE])=7 SET DATEFIRST 7 

Answer 2

Something like this is possible (don't forget to accept the answer)

 declare @t table(Date datetime, C1 int, C2 int) insert @t values('2012-08-01',44,44) insert @t values('2012-08-02',51,49) insert @t values('2012-08-03',60,59) insert @t values('2012-08-04',68,67) insert @t values('2012-08-05',82,78) insert @t values('2012-08-06',62,59) insert @t values('2012-08-07',58,53) insert @t values('2012-08-08',69,65) insert @t values('2012-08-09',82,72) insert @t values('2012-08-10',70,68) insert @t values('2012-08-11',75,71) insert @t values('2012-08-12',64,64) insert @t values('2012-08-13',74,69) insert @t values('2012-08-14',60,56) insert @t values('2012-08-15',66,60) insert @t values('2012-08-16',57,51) insert @t values('2012-08-17',52,49) select convert(varchar(10), dateadd(week, datediff(week, 0, date-1),0), 120)+' to '+ convert(varchar(10), max(dateadd(week, datediff(week, 0,date-1),6)), 120) Date, sum(C1) GRU, sum(C2) C1 from @t group by dateadd(week, datediff(week, 0, date-1),0) having datediff(day, 0, min(date)) %7 = 0