stored procedure to get date ranges

I have standard table that stores week range definitions

Range Name | StartWeek | EndWeek
Range A  | 26 | 36
Range B | 37 | 49
Range C | 50 | 8
Range D | 9 | 12
Range E | 13 | 18
Range F | 19 | 25

What I am looking for a stored procedure that will do the following using the user specified date range

Lets say the User enters a date range as follows
0701/2006 to 06/30/2007

Range A | 07/01/2006 | 09/16/2006
Range B | 09/17/2006 | 10/16/2006
Range C | 10/17/2006 | 3/03/2007
Range D | 03/04/2007 | 03/31/2007
Range E | 04/01/2007 | 05/12/2007
Range F | 05/13/2007 | 06/30/2007



krisredAsked:
Who is Participating?
 
twintaiCommented:
this make no sense. From StartWeek to EndWeek, is there set amount of days or weeks that you need to count. At any rate, Datepart(week,'10/07/07') will give you the week number. Depending on the server, a week is considered Sunday to Saturday. If you need the week to start with monday. Then you will need to add a line 'Set Datefirst 1' before your query. Lastly to get your remaining range, you can use a loop. Again you specify the relationship between the startweek and endweek. then i may be able to write an actual query.
0
 
ZberteocCommented:
I don't see what's the meaning of these number and how the two sets are related. Can you explain?
0
 
dbbishopCommented:
What is start week in relation to? There are 52 weeks/year. How do you know that 26 is week 26 of 2006, 2007 or 2001?
0
 
krisredAuthor Commented:
ok. Here;s a bit more info.

dbbishop: Thats right there are 52 weeks in a year.
Each Range starts at one week and ends a another week. you can see that there are no overlaps.
so based on the user specified date range, you will always start with Range A and move through F using the date range.

So in the above example user date range begins with 07/01/2006 and ends at 06/30/2007
so first I need which week the user specified start date falls in week 25, so need to start at range A because week 25 is between 26 and 36. Now we need find the end date for week 36 which is 09/16/2006 and move down the ranges until you reach the user specified enddate 06/30/2007

so if the user specified date range is 01/01/2007 to 06/30/2007
the need to start at Range C because 01/01/2007 is week 52 and falls between 50 and 8

hope this makes sense

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.