Generating Weekly data

[kickballmvp2006]

I have a table that holds daily data. There is a column in there I named TDate which holds the date value in the format ##/##/##. I want to query weekly data. What is the best way of doing this?

 

[kickballmvp2006]

I should be a little more specific....
For each month of the year, I want to be able to query data on a weekly basis based off the data I gave above. So I may want to get the totals for week 1, week 2, and so on.

 

[r937]

how do you define the weeks?

what database management system are you using?

r937.com | rudy.ca

 

[kickballmvp2006]

Weeks are based off the 4-5-4 calendar and right now I'm using Access but plan on putting it on MS SQL Server 2005.

 

[r937]

having worked with a 4-5-4 calendar in the past, i can tell you with complete honesty that the easiest way to proceed is with a calendar table

basically, generate a row for every date, with the date as the primary key, and as many data columns as you can think of that will prove useful, e.g. Year, Month, Day, dayofweek, isStatHoliday, calendarWeekno, fiscalWeekno, fiscalQuarter, etc.

then join the calendar table to your data on date, and GROUP BY the calendar column, e.g. fiscalWeekno

this method will require zero maintenance when converting to a different SQL language, whereas date functions typically require tweaking

4-5-4 fiscal periods are hell to calculate on the fly, and writing a user-defined function is needlessly complex



r937.com | rudy.ca