• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

SQL Query to get number of holidays between 2 dates using a holiday table in Oracle.

I have a table called CASE that has columns: ID, THEATER, CREATED_DATE, CLOSED_DATE
The column ID is the unique key in this table and the column THEATER has repeating values. (meaning: there could be multiple ID's for one THEATER).

There is another table called HOLIDAYS that has columns THEATER, HOLIDAY_DATE.
This table has all holiday dates listed for each THEATER.

The join between CASE and HOLIDAYS is on THEATER, but it is many:many relationship.

Now, for each CASE.ID I need to know the number of Holidays between the CREATED_DATE AND CLOSED_DATE values.

I need to achieve this in a sql query.


Thanks
0
MRPT
Asked:
MRPT
  • 2
  • 2
2 Solutions
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Try this:
SELECT ID, count(ID) NUM_HOLIDAYS
FROM
(
  SELECT *
  FROM CASES 
  FULL JOIN HOLIDAYS
  ON CASES.THEATER = HOLIDAYS.THEATER
)
WHERE HOLIDAY_DATE between CREATED_DATE and CLOSED_DATE
GROUP BY ID;

Open in new window


Sample tables:
create table CASES
(
  ID number(10) not null,
  THEATER varchar2(50),
  CREATED_DATE DATE,
  CLOSED_DATE DATE
);

create table HOLIDAYS
(
  THEATER varchar2(50),
  HOLIDAY_DATE DATE
);

insert into CASES
(ID, THEATER, CREATED_DATE, CLOSED_DATE)
values
(1, 'TH1', to_date('20141201','yyyymmdd'), to_date('20141212','yyyymmdd'));

insert into CASES
(ID, THEATER, CREATED_DATE, CLOSED_DATE)
values
(1, 'TH1', to_date('20141101','yyyymmdd'), to_date('20141112','yyyymmdd'));

insert into CASES
(ID, THEATER, CREATED_DATE, CLOSED_DATE)
values
(2, 'TH2', to_date('20141214','yyyymmdd'), to_date('20141218','yyyymmdd'));

insert into CASES
(ID, THEATER, CREATED_DATE, CLOSED_DATE)
values
(2, 'TH2', to_date('20140901','yyyymmdd'), to_date('20140930','yyyymmdd'));

insert into HOLIDAYS
(THEATER, HOLIDAY_DATE)
values
('TH1', to_date('20141205','yyyymmdd'));
 
insert into HOLIDAYS
(THEATER, HOLIDAY_DATE)
values
('TH1', to_date('20141209','yyyymmdd'));
 
 insert into HOLIDAYS
(THEATER, HOLIDAY_DATE)
values
('TH2', to_date('20140910','yyyymmdd'));

Open in new window


Sample output:
| ID | NUM_HOLIDAYS |
|----|--------------|
|  1 |            2 |
|  2 |            1 |

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
The sample data provided by Gerwin is not correct.  The original post states that the ID field in the CASES table is unique.  The sample data repeats the ID.

Also, I don't think the full outer join is needed.

I modified the sample to this:
CREATE TABLE cases 
  ( 
     id           NUMBER(10) NOT NULL, 
     theater      VARCHAR2(50), 
     created_date DATE, 
     closed_date  DATE 
  ); 

CREATE TABLE holidays 
  ( 
     theater      VARCHAR2(50), 
     holiday_date DATE 
  ); 

INSERT INTO cases 
            (id, 
             theater, 
             created_date, 
             closed_date) 
VALUES      (1, 
             'TH1', 
             To_date('20141201', 'yyyymmdd'), 
             To_date('20141212', 'yyyymmdd')); 

INSERT INTO cases 
            (id, 
             theater, 
             created_date, 
             closed_date) 
VALUES      (2, 
             'TH1', 
             To_date('20141101', 'yyyymmdd'), 
             To_date('20141112', 'yyyymmdd')); 

INSERT INTO cases 
            (id, 
             theater, 
             created_date, 
             closed_date) 
VALUES      (3, 
             'TH2', 
             To_date('20141214', 'yyyymmdd'), 
             To_date('20141218', 'yyyymmdd')); 

INSERT INTO cases 
            (id, 
             theater, 
             created_date, 
             closed_date) 
VALUES      (4, 
             'TH2', 
             To_date('20140901', 'yyyymmdd'), 
             To_date('20140930', 'yyyymmdd')); 

INSERT INTO cases 
            (id, 
             theater, 
             created_date, 
             closed_date) 
VALUES      (5, 
             'TH3', 
             To_date('20141207', 'yyyymmdd'), 
             To_date('20141216', 'yyyymmdd')); 

INSERT INTO holidays 
            (theater, 
             holiday_date) 
VALUES      ('TH1', 
             To_date('20141205', 'yyyymmdd')); 

INSERT INTO holidays 
            (theater, 
             holiday_date) 
VALUES      ('TH1', 
             To_date('20141209', 'yyyymmdd')); 

INSERT INTO holidays 
            (theater, 
             holiday_date) 
VALUES      ('TH2', 
             To_date('20140910', 'yyyymmdd')); 

COMMIT; 

Open in new window

Then used this query to get the result:
SELECT a.id, 
       SUM(Nvl2(b.holiday_date, 1, 0)) 
FROM   cases a 
       left outer join holidays b 
                    ON a.theater = b.theater 
GROUP  BY a.id; 

Open in new window

Result:
        ID SUM(NVL2(B.HOLIDAY_DATE,1,0))
---------- -----------------------------
         1                             2
         2                             2
         4                             1
         5                             0
         3                             1

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
Of course, then I went and didn't check the dates.

So, the query should be this:
SELECT a.id, 
       SUM(Nvl2(b.holiday_date, 1, 0)) 
FROM   cases a 
       left outer join holidays b 
                    ON a.theater = b.theater 
                       AND b.holiday_date BETWEEN 
                           a.created_date AND a.closed_date 
GROUP  BY a.id; 

Open in new window

With the output of:
        ID SUM(NVL2(B.HOLIDAY_DATE,1,0))
---------- -----------------------------
         1                             2
         2                             0
         4                             1
         5                             0
         3                             0

Open in new window

0
 
PortletPaulCommented:
Here are 2 options, one using a correlated subquery in the select clause - it could be acceptably efficient depending on your indexes and data volumes. Alternatively another using a join and group by.

It isn't necessary to use NVL() and SUM() to achieve the wanted count of holidays.
select
      id
    , theater
    , created_date
    , closed_date
    , (select count(*) 
        from holidays 
        where case.theater = holidays.theater 
        and holidays.holiday_date between case.created_date and case.closed_date
       ) num_holidays
from case
order by case.id
;

Open in new window


select
      case.id
    , case.theater
    , case.created_date
    , case.closed_date
    , count(holidays.holiday_date) num_holidays
from case
left join holidays on case.theater = holidays.theater 
        and holidays.holiday_date between case.created_date and case.closed_date
group by
      case.id
    , case.theater
    , case.created_date
    , case.closed_date
order by case.id
;

Open in new window


test cases:
**Oracle 11g R2 Schema Setup**:

    
    
    CREATE TABLE CASE 
    	("ID" int, "THEATER" int, "CREATED_DATE" timestamp, "CLOSED_DATE" timestamp)
    ;
    
    INSERT ALL 
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (1, 1, '01-Jan-2014 12:00:00 AM', '31-Jan-2014 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (2, 2, '01-Apr-2014 12:00:00 AM', '01-May-2014 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (3, 1, '30-Jun-2014 12:00:00 AM', '30-Jul-2014 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (4, 2, '28-Sep-2014 12:00:00 AM', '28-Oct-2014 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (5, 1, '27-Dec-2014 12:00:00 AM', '26-Jan-2015 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (6, 2, '27-Mar-2015 12:00:00 AM', '26-Apr-2015 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (7, 1, '25-Jun-2015 12:00:00 AM', '25-Jul-2015 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (8, 2, '23-Sep-2015 12:00:00 AM', '23-Oct-2015 12:00:00 AM')
    	INTO CASE  ("ID", "THEATER", "CREATED_DATE", "CLOSED_DATE")
    		 VALUES (9, 1, '22-Dec-2015 12:00:00 AM', '21-Jan-2016 12:00:00 AM')
    SELECT * FROM dual
    ;
    
    
    
    CREATE TABLE HOLIDAYS 
    	("THEATER" int, "HOLIDAY_DATE" timestamp)
    ;
    
    INSERT ALL 
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '01-Jan-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '28-Jan-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '24-Feb-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '23-Mar-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '19-Apr-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '16-May-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (1, '12-Jun-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '28-Jan-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '03-Mar-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '06-Apr-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '10-May-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '13-Jun-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '17-Jul-2014 12:00:00 AM')
    	INTO HOLIDAYS  ("THEATER", "HOLIDAY_DATE")
    		 VALUES (2, '20-Aug-2014 12:00:00 AM')
    SELECT * FROM dual
    ;

**Query 1**:

    select
          id
        , theater
        , created_date
        , closed_date
        , (select count(*) 
            from holidays 
            where case.theater = holidays.theater 
            and holidays.holiday_date between case.created_date and case.closed_date
           ) num_holidays
    from case
    order by case.id
    

**[Results][2]**:
    
    | ID | THEATER |                     CREATED_DATE |                    CLOSED_DATE | NUM_HOLIDAYS |
    |----|---------|----------------------------------|--------------------------------|--------------|
    |  1 |       1 |   January, 01 2014 00:00:00+0000 | January, 31 2014 00:00:00+0000 |            2 |
    |  2 |       2 |     April, 01 2014 00:00:00+0000 |     May, 01 2014 00:00:00+0000 |            1 |
    |  3 |       1 |      June, 30 2014 00:00:00+0000 |    July, 30 2014 00:00:00+0000 |            0 |
    |  4 |       2 | September, 28 2014 00:00:00+0000 | October, 28 2014 00:00:00+0000 |            0 |
    |  5 |       1 |  December, 27 2014 00:00:00+0000 | January, 26 2015 00:00:00+0000 |            0 |
    |  6 |       2 |     March, 27 2015 00:00:00+0000 |   April, 26 2015 00:00:00+0000 |            0 |
    |  7 |       1 |      June, 25 2015 00:00:00+0000 |    July, 25 2015 00:00:00+0000 |            0 |
    |  8 |       2 | September, 23 2015 00:00:00+0000 | October, 23 2015 00:00:00+0000 |            0 |
    |  9 |       1 |  December, 22 2015 00:00:00+0000 | January, 21 2016 00:00:00+0000 |            0 |


**Query 2**:

    select
          case.id
        , case.theater
        , case.created_date
        , case.closed_date
        , count(holidays.holiday_date) num_holidays
    from case
    left join holidays on case.theater = holidays.theater 
            and holidays.holiday_date between case.created_date and case.closed_date
    group by
          case.id
        , case.theater
        , case.created_date
        , case.closed_date
    order by case.id
    

**[Results][3]**:
    
    | ID | THEATER |                     CREATED_DATE |                    CLOSED_DATE | NUM_HOLIDAYS |
    |----|---------|----------------------------------|--------------------------------|--------------|
    |  1 |       1 |   January, 01 2014 00:00:00+0000 | January, 31 2014 00:00:00+0000 |            2 |
    |  2 |       2 |     April, 01 2014 00:00:00+0000 |     May, 01 2014 00:00:00+0000 |            1 |
    |  3 |       1 |      June, 30 2014 00:00:00+0000 |    July, 30 2014 00:00:00+0000 |            0 |
    |  4 |       2 | September, 28 2014 00:00:00+0000 | October, 28 2014 00:00:00+0000 |            0 |
    |  5 |       1 |  December, 27 2014 00:00:00+0000 | January, 26 2015 00:00:00+0000 |            0 |
    |  6 |       2 |     March, 27 2015 00:00:00+0000 |   April, 26 2015 00:00:00+0000 |            0 |
    |  7 |       1 |      June, 25 2015 00:00:00+0000 |    July, 25 2015 00:00:00+0000 |            0 |
    |  8 |       2 | September, 23 2015 00:00:00+0000 | October, 23 2015 00:00:00+0000 |            0 |
    |  9 |       1 |  December, 22 2015 00:00:00+0000 | January, 21 2016 00:00:00+0000 |            0 |



  [1]: http://sqlfiddle.com/#!4/024ee/7

Open in new window

0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
@johnsone >> The sample data provided by Gerwin is not correct.
You're right, renaming to 1,2,3,4 gives this result:

ID      NUM_HOLIDAYS
1      2
2      1
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now