Oracle : week within month / Business week

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).

Here is a part of the output. For this example I chose to include all the dates of the current year.

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.