Previous week / year query puzzler .

[proximity]

Hi,

Here's a hopefully easy one to solve, except I cant figure it out, of course!

In my query, I have a list of records returned along with their respective week numbers. How can I query it to bring me the last seventeen week numbers worth of data, so that it crosses years if necessary? See example below.
Wk Year
08 2008
07 2008
06 2008
05 2008
04 2008
03 2008
02 2008
01 2008
52 2007
51 2007
50 2007
49 2007
etc . . .

In the above example, I want a user to enter Wk 08 and for the query to return 17 weeks of data.

Any ideas?

 

[r937]

select top 17 wk,year
from daTable
order by year desc, wk desc




r937.com | rudy.ca

 

[proximity]

Thanks for the reply. However, that does not give me what I want - it will only return 17 records. I should have pointed out (apologies!) that there are hundreds of records and any number can have the same week no attached to them.

So, find every record with week 08, every record with week 07, every record with week 06 and so on going back 17 week numbers.

Hopefully, that should make it clearer?

 

[HarleyQuinn]

Join your outer query to Rudy's (which will return the last 17 weeks by week and year) on week and year then return everything in your main query and it will only show for those weeks in the inner query.

Hope this helps (and makes sense!)

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[lespaul]

I want a user to enter Wk 08

and assume that the year is the current year since you have a Wk 08 in EVERY year?


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[sxschech]

I'm doing a three year 6 week windowing technique that compares this week to last week and this year to last year and since it is an academic year, it crosses years too. There is quite a bit to it, with queries etc, but as far as the base table, here is a sample.
[tt]
RUNDATE WeekNo TERM
9/16/2005 200600 200620
9/23/2005 200601 200620
9/30/2005 200602 200620
...
...
9/15/2006 200700 200720
9/22/2006 200701 200720
9/29/2006 200702 200720
[/tt]