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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Generating Weekly data

Status
Not open for further replies.

kickballmvp2006

Technical User
Jan 4, 2007
30
US
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?
 
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.
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top