Forum Stats

  • 3,752,288 Users
  • 2,250,483 Discussions
  • 7,867,783 Comments

Discussions

Holiday Count between dates

Newuser1
Newuser1 Member Posts: 280 Blue Ribbon
edited Jul 23, 2021 2:18PM in SQL & PL/SQL

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,

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,925 Red Diamond

    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
    mathguy Member Posts: 10,009 Gold Crown

    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?