Monday, July 7, 2008

Query to get the number of working days between two given dates

A friend of mine talked about a requirement where reminder mails had to be sent out to managers who had leave requests from their subordinates which were unapproved for the last five 'working days'. Working Days meant all days between two days excluding saturday, sunday and statutory holidays.
While this can be easily achieved by a two step process, i.e. - looping through all days between the two given days and checking individually whether the day is non-working ir not, I felt that it would be interesting to get the desired output in a single SQL query.
This is what transpired:
Pseudo Code:
1. Get all the days between the two dates in an inline view.
2. Exclude saturdays and sundays from the above result set.
3. Exclude all holidays that fall in the given date range by checking the holiday schedule of the employee.

The below query fetches the working days between 01 Jan 08 and 10 Jan 08. Note that the logic to exclude holidays is constructed from a Peoplesoft point of view.
select daynum from
(select to_date('31-DEC-2007','DD-MON-YYYY') + rownum as daynum
from
all_tables
where
rownum<=(select to_date('10-JAN-2008','DD-MON-YYYY') - to_date('31-DEC-2007','DD-MON-YYYY') from dual))
where to_char(daynum,'DY') not in ('SAT','SUN')
and daynum not in (select holiday from ps_holiday_date where holiday_schedule = 'CHEG' and holiday between '01-JAN-2008' and '10-JAN-2008')

1 comment:

Bloke T said...

what if Sat is a standard working day? You could do this:
"select datediff(dd,'2009-Feb-09', '2009-feb-14')
-floor((datepart(dw,'2009-Feb-06')
+datediff(dd,'2009-Feb-09', '2009-feb-14')) /7 )*2"
but it doesn't calculate Saturdays properly. Any ideas?