Case statement in a Group By???

[NEveritt]

Is it possible to put a case statement in a the group by part of a query? Below is an example of a query using the normal Datepart function for the week number, and then the ISO week function to get the correct week number. However in using the ISO function, you have to use a Case Statment, which wont work, as yet, in the Group part. Hope that makes sense.


THIS WORKS

SELECT
datepart(ww,[timestamp]) as 'week'
from dbo.casehistories
group by
datepart(ww,[timestamp])

THIS DOESNT WORK

SELECT
'Week' = case when cast(dbo.ISOweek(dbo.casehistories.[timestamp])as varchar(2)) <10 then cast(datepart(yy,dbo.casehistories.[timestamp])as varchar(4))+ '-' + '0'+ cast(dbo.ISOweek(dbo.casehistories.[timestamp]) as varchar(1)) else cast(datepart(yy,dbo.casehistories.[timestamp])as varchar(4))+ '-' + cast(dbo.ISOweek(dbo.casehistories.[timestamp]) as varchar(2)) end

from dbo.casehistories
group by 'Week' = case when cast(dbo.ISOweek(dbo.casehistories.[timestamp])as varchar(2)) <10 then cast(datepart(yy,dbo.casehistories.[timestamp])as varchar(4))+ '-' + '0'+ cast(dbo.ISOweek(dbo.casehistories.[timestamp]) as varchar(1)) else cast(datepart(yy,dbo.casehistories.[timestamp])as varchar(4))+ '-' + cast(dbo.ISOweek(dbo.casehistories.[timestamp]) as varchar(2)) end

 

[vongrunt]

Remove assignment ('Week'=) part from GROUP BY and try again.

Or better not. This code is a CPU killer.



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[GShen]

What are you trying to obtain with the 2nd Select which is different than the 1st? Show me some output please.
Also, is there a reason you are using this ISO function? You stated TO GET THE CORRECT WEEK NUMBER. Never used it before and I can't find it anywhere.

Remember when... everything worked and there was a reason for it?

 

[NEveritt]

Thanks to Vongrunt, it was that simple!

For GShen - the query was a little more complicated as I make a year-week code out of the case statement, i.e. week 12 in 2004 is 2204-12.

I use ISO week (ISO standard) as thats what our company use, this is the ISO standard for week numbering. If you look into it closely, ISO weeks are different from the Microsoft week, i.e. for example ISO week for 28th February 2005 is 9. However the Microsoft week is 10 etc etc. Its all about what is week 1 in a year when there is only a few days in the first week etc.