Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculating Fields based on a Month

Status
Not open for further replies.

renee35

MIS
Jan 30, 2007
199
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
 
Not sure if what you are looking for is the
Code:
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top