June 24, 2011 at 8:17 am
I've got a table with a large number of rows. Each row contains a user_id, a decimal number (hours worked) and a date. I need to be able to pull out the user_id and then group all of the hours into weeks. So, for example, I'll have the following rows:
User_id: 6, Hours: 7, Date: 12-08-2011
User_id: 6, Hours: 5, Date: 12-06-2011
User_id: 6, Hours: 8, Date: 15-06-2011
After the grouping (or sub-queries or whatever works), you would have the following:
User_id: 6, Week 1: 7, Week 2: 13, etc
I'm linking this with a users table (based on the user_id) to get the names. I need to return a row for everyone, even if they have no hours (in which case I would return zero in that field).
Does that make sense? Is this sort of thing possible?
June 24, 2011 at 12:42 pm
First you need to define what a week is (e.g. does it start on Sunday or Monday or any other day? Is it based on ISO week (52 or 53 weeks in a year) or a financial week (always 52 weeks)?).
Second you need to define how to number those weeks (why is a week in Augsut numbered as 1 and a different week two month before numbered as 2?)
Third, you need to clarify the term "one row for everyone: does it need to contain all weeks of a given period with zero numbers if there are no rows in your original table or does it just require to include users that are not listed at all in the table for the period in question?
After those questions are answered you need to find the function to calculate the corresponding week of a date. Then you can simply group by that value and aggregate the hours worked per user. Or use a calendar table.
Finally, use a CrossTab or DynamicCrssTab to get the pivoted result as requested. Or, even better, perform the pivot at the front end layer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply