3

I use tsql to sum weekly data (starting date is Monday). Last week spanned across the boundary of 2012 and 2013, which means that I get two weekly sums. Is there a way to group the data within tsql so I get one weekly sum for 2012-12-31 to 2013-01-06?

Example data and sql: http://sqlfiddle.com/#!6/52b25/13/0

No link example below. Hypothetical table has one column, "created" of datetime type. How many rows for each week? I'd like to get two result rows, one for the last solid week of 2012 and one for the week that 2013 starts in (which starts on Monday 12/31/2012).

select 
min(created) as 'First Date',
count(*) as 'Count',
datepart(wk,created) as 'Week Number'
from items
group by  datepart(wk,created), datepart(yy,created)
order by min(created)
Adolph Trudeau
  • 351
  • 1
  • 8

1 Answers1

5

One workaround is calculate week number manually

select 
min(created) as 'First Date',
count(*) as 'Count',
 datediff(dd,'2012-01-02',created)/7 AS week_number
from items
group by  datediff(dd,'2012-01-02',created)/7  -- You may replace '2012-01-02' to any date that is Monday (start day of the week)
order by min(created)
EricZ
  • 6,065
  • 1
  • 30
  • 30