Holiday Count between dates

Newuser1

Hi Team,

We have to find the count of working days between 2 dates (Job_Start_Date and Job_End_Date) it has to exclude weekends and public holidays using JOB (it’s a type 2 table have to use Curr_flag=’Y’ for latest data) and CALENDER tables.

JOB table contains Job_Start_Date and Job_End_Date and CALENDER table contains holiday and weekend information Holiday_flag (‘Y’,’N’) and Weekend_flag (‘Y’,’N’).

So using JOB table first we are calculating the Day_Diff_Count between 2 dates and Weekend_Count between 2 dates using below query.

Query:

SELECT A.JOB_ID, A.JOB_START_DATE, A.JOB_END_DATE,

ROUND(JOB_END_DATE)-(JOB_START_DATE)) as Day_Diff_Count, 

least(7-to_char(FORECASTED_START_DATE,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+1,2)

+greatest (to_char (ACTUAL_COMPLETION_DATE,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')-5,0)

+ ((trunc(ACTUAL_COMPLETION_DATE,'IW')-trunc(FORECASTED_START_DATE,'IW'))/7-1)*2 as Weekend_Count

from JOB A

where

A.CURR_FLG=’Y’ ------ this query is working for weekend count

How to find Holiday_Count from CALENDER and JOB tables, also if holiday is weekend then it should not count (bcoz in the above query we already find the weekend count) , here we have to use TIME_KEY to join with JOB table using between dates like below query.

SELECT A.JOB_ID, A.JOB_START_DATE, A.JOB_END_DATE,

ROUND(JOB_END_DATE)-(JOB_START_DATE)) as Day_DIFF_Count, 

least(7-to_char(FORECASTED_START_DATE,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+1,2)

+greatest (to_char (ACTUAL_COMPLETION_DATE,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')-5,0)

+ ((trunc(ACTUAL_COMPLETION_DATE,'IW')-trunc(FORECASTED_START_DATE,'IW'))/7-1)*2 as Weekend_Count,

------------- as Holiday_Count

from JOB A , CALENDER B

where B.TIME_KEY between A.JOB_START_DATE and A.JOB_END_DATE

and A.CURR_FLG=’Y’

Finally we will add weekend_count and Holiday_Count and minus with Day_Diff_Count to get exact working days.

Can someone suggest on this, hope you got my issue/task.

Thanks in advance,

Frank Kulash

Hi, @Newuser1

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

least(7-to_char(FORECASTED_START_DATE,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+1,2)

+greatest (to_char (ACTUAL_COMPLETION_DATE,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')-5,0)

+ ((trunc(ACTUAL_COMPLETION_DATE,'IW')-trunc(FORECASTED_START_DATE,'IW'))/7-1)*2 as Weekend_Count

That looks like you might calculate how many weekend days are in a range without using a calendar table. Since you do have a calendar table, it's simpler just to use it, especially since you have to use the calendar table anyway, to get the holiday information.

mathguy

What is a "type 2 table"?

Anyway - why are you trying to calculate total days, then holiday days and weekend days separately, and then holidays that fall on a weekend, and play with those numbers to get the result?

Why not simply select count(*) from the calendar table, where the dates are between the job start date and the job end date and both flags (for "holiday" and "weekend") are 'N' ? Isn't that a trivial computation? Or do you not "see" this trivial solution?