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 Go to next message
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




Re: Delays between two dates excluding holidays/weekends [message #661029 is a reply to message #660954] Sun, 05 March 2017 20:45 Go to previous messageGo to next message
JohnGuru
Messages: 6
Registered: March 2017
Junior Member
I feel that this is the best way of doing as Holiday table is not a huge table & will not harm the performance if it is used in Select clause. You may use 1 instead of "*".
Re: Delays between two dates excluding holidays/weekends [message #661034 is a reply to message #661029] Mon, 06 March 2017 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You may use 1 instead of "*".
Explain.

Quote:
I feel that this is the best way of doing as Holiday table is not a huge table & will not harm the performance if it is used in Select clause.
What is the rationale?
You feel? Is computer science a matter of feeling?

Re: Delays between two dates excluding holidays/weekends [message #661045 is a reply to message #661029] Mon, 06 March 2017 02:58 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
JohnGuru wrote on Mon, 06 March 2017 02:45
You may use 1 instead of "*".
You may, but you shouldn't. It's a myth that count(1) is faster than count(*) and oracle internally rewrites count(1) to count(*).
Previous Topic: why cbo select FULL TABLE SCAN
Next Topic: Analytical query
Goto Forum:
  


Current Time: Fri Mar 29 08:10:38 CDT 2024