Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Holiday Count between dates

Newuser1Jul 23 2021 — edited Jul 23 2021

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,

Comments

Frank Kulash

Hi, @newuser11
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?

1 - 2

Post Details

Added on Jul 23 2021
#sql
2 comments
828 views
1 mention