I have a table which contains a start date and an end date
along with this i have another table which has the list of all bank holidays
date day_of_week bank holiday
13-06-2013 4 N
using these two tables i need to calculate the number of working days between the start date and end date of table A
Any ideas on how to proceed with this
which i have derived from sys.calendar and the bank holiday list
I have created the below tables for your scenario.Let me know if it solves your purpose.
CT HOL(START_DATE DATE,END_DATE DATE);
INS INTO HOL VALUES('2013-06-03','2013-06-13');
INS INTO HOL VALUES('2013-06-06','2013-06-12');
INS INTO HOL VALUES('2013-06-05','2013-06-22');
CT LIST(HOL_DATE DATE)
INS INTO LIST VALUES('2013-06-01');
INS INTO LIST VALUES('2013-06-02');
INS INTO LIST VALUES('2013-06-09');
INS INTO LIST VALUES('2013-06-08');
INS INTO LIST VALUES('2013-06-15');
INS INTO LIST VALUES('2013-06-16');
INS INTO LIST VALUES('2013-06-22');
INS INTO LIST VALUES('2013-06-23');
(SEL START_DATE,END_DATE , L.HOL_DATE,CASE WHEN L.HOL_DATE BETWEEN H.START_DATE AND H.END_DATE THEN 'Y' ELSE 'N' END AS WORKING_DAYS FROM HOL H,LIST L )DT
GROUP BY 1,2
--I did'nt join any key for two tables,you can join if anything in common.
I dont think it quite solves the problem.
you are taking those dates which fall in betwen the dates and marking them as yes, but the second table is already a list of holiday
you query is giving out put
03/06/2013 13/06/2013 6
it says 6 working days but there are tqo holidays between these dats so ans should come 11
this can be further simplified, you don't the sys_calendar:
SELECT start_date, end_date,
end_date-start_date+1 AS period_length,
period_length - holiday_days AS working_days,
SUM(CASE WHEN b.bank_holiday='Y' THEN 1 ELSE 0 END) AS holiday_days
FROM a LEFT JOIN b
ON b.hol_date BETWEEN a.start_date AND a.end_date
GROUP BY 1,2
ORDER BY 1,2
Of course this kind of calculation involves a CROSS join due to non-equality, better don't try it on a huge table :-)
When this calculation must be done repeatedly i usually recommend adding the holiday information to your (hopefully) existing calendar table.
Then add another INT column which is populated using
SUM(CASE WHEN bank_holiday = 'Y' THEN 0 ELSE 1 END) OVER (ORDER BY calendar_date ROWS UNBOUNDED PRECEDING) AS WorkDay#
Now you got a running number for each date which is only increased for working days and the calculation uses two equi-joins (instead of the bad product join) and a simple a.WorkDay# - b.WorkDay#
To get the business days(other than sunday,satday, public holidays) data in a table, do we need to manually insert the holiday list and then query on that holiday table?? or anyother option available?
As I have requirement where my jobs need to run monthly once, on 1st Business day of the month.