How can I group information by week?

[majorbroncosfan]

I'm trying to do an analysis of employee time by week. How can I get that to group in that order? Is that something that would have to be done in something like Crystal Reports?

 

[dvrobin]

How is the date information stored? Is it a datetime field or is it in integer format? You will want to group by a calculated field which indicates the week. There is no reason why you can't build a query to do that. It would look prettier in Crystal, but you need the ability to run the query for Crystal to work anyway, so you'll have to build it eventually.

Dave Robinder, MCSD

 

[majorbroncosfan]

It is a datetime field.

 

[dvrobin]

Ok, you'll want to derive the week based on the daten and group on the derived week field. The easiest way to do this would probably be to create a lookup table for the weeks of the year. It would include the beginning and end date for each week as well as the week number.

After doing that, try using something similar to the following query:

select week, employeename, sum(hours) from mytable inner join MyWeeks on MyWeeks.BeginDate<=mytable.WorkDate and MyWeeks.EndDate>=mytable.WorkDate Group By employeename, week

Dave Robinder, MCSD