krisred
asked on
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
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
Zberteoc
I don't see what's the meaning of these number and how the two sets are related. Can you explain?
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.