Calculating Fields based on a Month

[renee35]

Below is the current code I have summing data based on a month, how can I get it to be broken down by week?

Here is the code:
[Jan] = sum( case when Month( WeekEnding) = 1 then isnull( e.AllTCHours, 0) else 0 end ),
[Feb] = sum( case when Month( WeekEnding) = 2 then isnull( e.AllTCHours, 0) else 0 end ),
[Mar] = sum( case when Month( WeekEnding) = 3 then isnull( e.AllTCHours, 0) else 0 end ),
[Apr] = sum( case when Month( WeekEnding) = 4 then isnull( e.AllTCHours, 0) else 0 end ),
[May] = sum( case when Month( WeekEnding) = 5 then isnull( e.AllTCHours, 0) else 0 end ),

What I am trying to accomplish is to have the data print by Week1, Week2, Week3, etc. as opposed to Jan, Feb, Mar

Any suggestions would be greatley appreciated.

Thanks!!

Thanks a bunch!!

-T

 

[hmckillop]

Not sure if what you are looking for is the

DATEPART(ww, @myDateField)
--e.g.
declare @myDateField datetime
set @myDateField = '04-mar-2009'
select DATEPART(ww, @myDateField) --returns 10 which is the 10th week of year

"I'm living so far beyond my income that we may almost be said to be living apart

 

[renee35]

It current prints out like this:

Jan Feb Mar Apr
2 4 13 10

What I would like to do is this:

Week1 Week2 Week3 Week4
2 4 13 10

based on the parameter being passed:

209-2-1 --> 2009-2-28

Thanks!

Thanks a bunch!!

-T

 

[renee35]

I got it! Thanks for that information because that is what helped me figure it out. This is what I ended up with:

[Week6] = sum( case when DATEPART(ww, weekending) = 6 then isnull( e.AllTCHours, 0) else 0 end ),
[Week7] = sum( case when DATEPART(ww, weekending) = 7 then isnull( e.AllTCHours, 0) else 0 end ),
[Week8] = sum( case when DATEPART(ww, weekending) = 8 then isnull( e.AllTCHours, 0) else 0 end ),
[Week9] = sum( case when DATEPART(ww, weekending) = 9 then isnull( e.AllTCHours, 0) else 0 end ),

Thanks a bunch!!

-T