Access query (SQL) to return records sorted (grouped by) WEEKS

Question

Greetings SQL gurus,

I don't know if you can help me, but I will try. I have several large databases grouped by year (each year in a different database). I want to be able to compare values from a particular week from one year to the next. For example, "show me week 17 of 2008 vs. week 17 of 2002."

I have the following definition of weeks that ideally I would use:

1. Only 52 weeks each year and 7 days a week (that only takes 364 days),
2. The first day of the first week starts from January 2nd - which means we do not use January 1st data, and
3. In leap year, the first day of the first week ALSO starts from the January 2nd plus we skip Feb. 29.

Any ideas?

Thanks in advance.

Answer 1

To get the week number

 'to get the week number in the year
 select datepart( week, datefield)

 'to get the week number in the month
 select (datepart(dd,datefield) -1 ) / 7 + 1

You don't need to complicate things thinking about leap years, etc. Just compare weeks mon to sun

Answer 2

SInce you havea a specifc defintion of when the week starts that is differnt that the standard used by the db, I think a weeks table is the solution to your problem. For each year create a table that defines the dates contained in each week and the week number. Then by joining to that table as well as the relevant other tables, you can ask for just the data for week 17.

Table structure
Date      Week
20090102   1
20090103   1

etc.

Answer 3

Best to avoid creating a table because then you have to update and maintain it to get your queries to work.

DatePart('ww',[myDate]) will give you the week number. You may run into some issues though deciding which week belongs to which year - for example if Jan 1 2003 is on Wednesday does the week belong as week 52 in 2002 or week 1 in 2003? Your accounting department will have a day of the week that is your end of week (usually Sat). I usually just pick the year that has the most days in it. DatePart will always count the first week as 1 and in the case of the example above the last week as 53. You may not care that much either way. You can create queries for each year

SELECT DatePart('ww',[myDate]) as WeekNumber,myYearTable.* as WeekNumber 
FROM myYearTable

and then join the queries to get your data. You'll loose a couple days at the end of the year if one table has 52 weeks and one has 53 (most will show as 53). Or you can do it by your weekending day - this always gives you Saturday which would push a late week into the following year.

(7-Weekday([myDate]))+[myDate]

then

DatePart('ww',(7-Weekday([myDate]))+[myDate])

Hope that helps