Home » SQL & PL/SQL » SQL & PL/SQL » Delays between two dates excluding holidays/weekends (Oracle 11.2.0.3)
Delays between two dates excluding holidays/weekends [message #660954] |
Thu, 02 March 2017 08:17 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a holiday table, which stores weekends as well as holidays
CREATE TABLE SCOTT.T_HOLIDAYS
(
HOLIDAY_DT DATE,
HOLIDAY_DESC VARCHAR2(20 BYTE)
);
begin
insert into t_holidays values (to_date('03/4/2017','MM/DD/YYYY'),'weekend-sat');
insert into t_holidays values(to_date('03/5/2017','MM/DD/YYYY'),'weekend-sun');
insert into t_holidays values(to_date('03/6/2017','MM/DD/YYYY'),'holiday');
insert into t_holidays values(to_date('03/8/2017','MM/DD/YYYY'),'holiday');
insert into t_holidays values(to_date('03/10/2017','MM/DD/YYYY'),'holiday');
insert into t_holidays values(to_date('03/11/2017','MM/DD/YYYY'),'weekenda-sat');
insert into t_holidays values(to_date('03/12/2017','MM/DD/YYYY'),'weekenda-sun');
insert into t_holidays values(to_date('03/14/207','MM/DD/YYYY'),'holiday');
end;
/
There is other table which stores two dates for each case id i.e. from_dt and to_dt,
now i would like to calculate the difference between to_dt and from_dt and while calculating the delay I have to join the
holidays table to not consider the holidays/weekends in the delay.
create table t_case (case_no varchar2(20), from_dt date, to_dt date, delay number)
begin
insert into t_case (case_no,from_dt, to_dt) values ('SP0202',to_date('03/1/2017','MM/DD/YYYY'),to_date('03/3/2017','MM/DD/YYYY'));
insert into t_case (case_no,from_dt, to_dt) values ('SP0203',to_date('03/1/2017','MM/DD/YYYY'),to_date('03/5/2017','MM/DD/YYYY'));
insert into t_case (case_no,from_dt, to_dt) values ('SP0204',to_date('03/6/2017','MM/DD/YYYY'),to_date('03/9/2017','MM/DD/YYYY'));
insert into t_case (case_no,from_dt, to_dt) values ('SP0205',to_date('03/9/2017','MM/DD/YYYY'),to_date('03/15/2017','MM/DD/YYYY'));
end;
/
Now, I have written something like below.
Do you find any other ways/better ways of doing it,
SELECT case_no,
from_dt,
to_dt,
to_dt - from_dt-
(SELECT COUNT (*)
FROM t_holidays
WHERE holiday_dt BETWEEN from_dt AND to_dt) delay
FROM t_case;
Thank you in advance.
Regards,
Pointers
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 08:10:38 CDT 2024
|