Here is a piece of sql I wrote recently to get the week ranges for a given set of dates. The two requirements that are different in this case are..
1. The week starts on Monday.
2. The week number and range is reset for each month. This means if a month starts on say..Wednesday, the first business week for that month would be from the wednesday through the first sunday. Also, if a week ends on say..Thursday, the last week of the month would only be from the last monday through the end of the month (thursday).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT date1, week_within_month_start, week_within_month_end, TO_CHAR (week_within_month_start, 'DD-Mon-YYYY') || ' - ' || TO_CHAR (week_within_month_end, 'DD-Mon-YYYY') week_range FROM (SELECT date1, TRUNC (date1 - 1, 'DAY') + 1 monday_of_the_week, TRUNC (date1, 'MM') begin_of_month, GREATEST (TRUNC (date1 - 1, 'DAY') + 1, TRUNC (date1, 'MM')) week_within_month_start, TRUNC (date1 - 1, 'DAY') + 7 sunday_of_the_week, TRUNC (LAST_DAY (date1)) end_of_month, LEAST (TRUNC (date1 - 1, 'DAY') + 7, TRUNC (LAST_DAY (date1)) ) week_within_month_end FROM (--- Generate all the dates in the given year... SELECT (TRUNC (SYSDATE, 'YY') + LEVEL - 1) date1 FROM DUAL CONNECT BY LEVEL <= ( ADD_MONTHS (TRUNC (SYSDATE, 'YY'), 12) - TRUNC (SYSDATE, 'YY') ))) ORDER BY date1 |
Here is a part of the output. For this example I chose to include all the dates of the current year.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DATE1 WEEK_WITH WEEK_WITH WEEK_RANGE --------- --------- --------- ------------------------- 01-JAN-11 01-JAN-11 02-JAN-11 01-Jan-2011 - 02-Jan-2011 02-JAN-11 01-JAN-11 02-JAN-11 01-Jan-2011 - 02-Jan-2011 03-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 04-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 05-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 06-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 07-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 08-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 09-JAN-11 03-JAN-11 09-JAN-11 03-Jan-2011 - 09-Jan-2011 10-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 11-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 DATE1 WEEK_WITH WEEK_WITH WEEK_RANGE --------- --------- --------- ------------------------- 12-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 13-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 14-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 15-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 16-JAN-11 10-JAN-11 16-JAN-11 10-Jan-2011 - 16-Jan-2011 17-JAN-11 17-JAN-11 23-JAN-11 17-Jan-2011 - 23-Jan-2011 18-JAN-11 17-JAN-11 23-JAN-11 17-Jan-2011 - 23-Jan-2011 19-JAN-11 17-JAN-11 23-JAN-11 17-Jan-2011 - 23-Jan-2011 |
This is a frequent requirement and the week-range is generally considered the “business week” for some of our recent projects.
I have seen this implemented in PL/SQL ,but you can do it in plain and simple sql as shown above.
Hopefully this is helpful for your own similar requirements in the future..
Cheers!
Rajesh.