Determine number working day between two dates

Database
Enthusiast

Determine number working day between two dates

Hi Guys,

I have a table which contains a start date and an end date

Table a

Start_date       End_date

13-03-2013     14-03-2013

10-03-2013     13-03-2013

.........

....

along with this i have another table which has the list of all bank holidays

table b

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 

6 REPLIES
KVB
Enthusiast

Re: Determine number working day between two dates

Hi

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 DT.START_DATE,DT.END_DATE,COUNT(DT.WORKING_DAYS)

FROM

(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

WHERE DT.WORKING_DAYS='N'

--I did'nt join any key for two tables,you can join if anything in common.

Regards

KVB

Enthusiast

Re: Determine number working day between two dates

hi Bikky,

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

eg

 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 

Teradata Employee

Re: Determine number working day between two dates

CT A(START_DATE DATE,END_DATE DATE);

INS INTO A VALUES('2013-06-03','2013-06-13');

INS INTO A VALUES('2013-06-06','2013-06-12');

INS INTO A VALUES('2013-06-05','2013-06-22');

CT B(HOL_DATE DATE, BANK_HOLIDAY CHAR(1));

INS INTO B VALUES('2013-06-01','Y');

INS INTO B VALUES('2013-06-02','Y');

INS INTO B VALUES('2013-06-09','Y');

INS INTO B VALUES('2013-06-08','Y');

INS INTO B VALUES('2013-06-15','Y');

INS INTO B VALUES('2013-06-16','Y');

INS INTO B VALUES('2013-06-22','Y');

INS INTO B VALUES('2013-06-23','Y');

select start_date, end_date, 

    end_date-start_date+1 as period_length, 

    sum(case when b.bank_holiday='Y' then 0 else 1 end) as working_days,

    sum(case when b.bank_holiday='Y' then 1 else 0 end) as holiday_days

from a

join sys_calendar.CALENDAR as c

on c.calendar_date between a.start_date and a.end_date

left join b ON c.calendar_date = b.hol_date

group by 1,2

order by 1,2

Junior Contributor

Re: Determine number working day between two dates

Hi Vlad,

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#

Dieter

Teradata Employee

Re: Determine number working day between two dates

Hi Dieter,

thank you for providing the equi-joins option, it's really cool!

With kind regards,

Vlad.

Fan

Re: Determine number working day between two dates

Hi Dieter,

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.

Regards,

Indu