Hi Everyone,
We have Job_details and time tables and task is to find the count of business /working days between start_date and end_date and it should exclude weekend and holidays count from time table.
We have to join the 2 tables using
Time.Time_key between job_details.start_date and job_details.end_date
Can someone suggest on this to achieve the count Using Version: Oracle SQL 20.2.0.175
Sample script:
create table job_details (Job_ID VARCHAR2(100), Start_date DATE, End_date DATE, Job_Type VARCHAR2(100));
insert into job_details values( 'AAA- 1500','28-JAN-20','28-JAN-20','AAA');
insert into job_details values('AAA- 1501','22-JUN-20','25-JUN-20','AAA');
insert into job_details values( 'AAA- 1502','08-MAY-20','12-MAY-20','AAA');
insert into job_details values( 'BBB- 1600','09-NOV-21','13-NOV-21','BBB');
insert into job_details values( 'BBB- 1601','15-JUL-21','19-JUN-21','BBB');
insert into job_details values( 'BBB- 1602','20-MAY-21','25-MAY-21','BBB');
create table time ( TIME_KEY DATE,HOLIDAY_FLAG CHAR(1), WEEKEND_FLAG VARCHAR2(1), YEAR NUMBER(4));
insert into time values( '01-JAN-20','Y','Y', '2020');
insert into time values( '10-JAN-20','N','Y', '2020');
insert into time values( '25-JAN-20','Y','N', '2020');
insert into time values( '01-FEB-21','N','Y', '2021');
insert into time values( '15-MAR-20','N','N', '2021');
insert into time values( '25-JUN-20','Y','Y', '2021');
Regards & thanks,