Forum Stats

  • 3,757,262 Users
  • 2,251,216 Discussions
  • 7,869,781 Comments

Discussions

Count of business/working days

User_ZHKQ3
User_ZHKQ3 Member Posts: 2 Green Ribbon

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,

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Ok, so if you take the start date away from the end date you'll get the number of days between (you may want to add 1 if you need inclusive days)

    You can join your table to your calendar table where the calendar date is between your start and end dates and substract the number of records in that that are considered holiday/weekend days.

    So, show what you've tried and what's not working for you.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    Hi, @User_ZHKQ3

    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');

    Don't try to insert a string (such as '01-JAN-20') into a DATE column (such as time_key). Use TO_DATE, with four digit years, or DATE literals

    Problems like this are much simpler and faster if you have a calendar table that has one row per day, whether it is a working day or not, and a column that give the total number of working days since some fixed point. For an example, see PL/SQL function to calculate non-working days — oracle-tech

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    As frank says, you should ensure dates are treated as dates, and ensure 4 digit years (is 01-JAN-20 the 1st January 2020 or 1st January 1920 or 20th January 2001 or 20th January 1901? Always be clear.) Your data also seems flawed... one of your job details has an end date before the start date, and your time table has dates that don't match the year column. Avoid using "time" as a name for an object. Whilst it's not strictly a documented reserved word, it does have a known use (just as DATE literal does).

    Also, if you're going to post example data, try and make sure it has some relevant to each other so that there's a good example of different situations. i.e. make sure there are some holidays and some weekends in a couple of the jobs date ranges.

    Anyway, with some fudging to the data... here's a quick example...

    SQL> with job_details (Job_ID, Start_date, End_date, Job_Type) as (
      2    select 'AAA- 1500', date '2020-01-28', date '2020-01-28', 'AAA' from dual union all
      3    select 'AAA- 1501', date '2020-06-22', date '2020-06-25', 'AAA' from dual union all
      4    select 'AAA- 1502', date '2020-05-08', date '2020-05-12', 'AAA' from dual union all
      5    select 'BBB- 1600', date '2021-11-09', date '2021-11-13', 'BBB' from dual union all
      6    select 'BBB- 1601', date '2021-06-15', date '2021-07-19', 'BBB' from dual union all
      7    select 'BBB- 1602', date '2021-05-20', date '2021-05-25', 'BBB' from dual
      8    )
      9   ,cal (TIME_KEY, HOLIDAY_FLAG, WEEKEND_FLAG, YEAR) as (
     10    select date '2020-01-01','Y','Y', '2020' from dual union all
     11    select date '2020-01-10','N','Y', '2020' from dual union all
     12    select date '2020-01-25','Y','N', '2020' from dual union all
     13    select date '2021-02-01','N','Y', '2021' from dual union all
     14    select date '2020-03-15','N','N', '2021' from dual union all
     15    select date '2020-06-25','Y','Y', '2021' from dual
     16    )
     17  select j.job_id
     18        ,j.job_type
     19        ,j.start_date
     20        ,j.end_date
     21        ,j.end_date-j.start_date+1 as total_days
     22        ,count(case when c.time_key is not null then 1 end) as hol_we
     23        ,j.end_date-j.start_date+1-count(case when c.time_key is not null then 1 end) as worked_days
     24  from   job_details j
     25         left outer join cal c on (c.time_key between j.start_date and j.end_date and 'Y' in (c.holiday_flag, c.weekend_flag))
     26  group by j.job_id
     27        ,j.job_type
     28        ,j.start_date
     29        ,j.end_date
     30  order by 1
     31  /
    
    JOB_ID    JOB START_DATE           END_DATE             TOTAL_DAYS     HOL_WE WORKED_DAYS
    --------- --- -------------------- -------------------- ---------- ---------- -----------
    AAA- 1500 AAA 28-JAN-2020 00:00:00 28-JAN-2020 00:00:00          1          0           1
    AAA- 1501 AAA 22-JUN-2020 00:00:00 25-JUN-2020 00:00:00          4          1           3
    AAA- 1502 AAA 08-MAY-2020 00:00:00 12-MAY-2020 00:00:00          5          0           5
    BBB- 1600 BBB 09-NOV-2021 00:00:00 13-NOV-2021 00:00:00          5          0           5
    BBB- 1601 BBB 15-JUN-2021 00:00:00 19-JUL-2021 00:00:00         35          0          35
    BBB- 1602 BBB 20-MAY-2021 00:00:00 25-MAY-2021 00:00:00          6          0           6
    
    
    6 rows selected.
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    Hi,

    insert into job_details values( 'BBB- 1601','15-JUL-21','19-JUN-21','BBB');

    Is this a mistake? Start_date is later than end_date.

    When you post the query (as Blushadow said) post the exact results you want from the given data.

  • mathguy
    mathguy Member Posts: 10,068 Gold Crown
    • Why did you include the MySQL tag? Are you asking an Oracle SQL question, or a MySQL question? Or do you believe that the two are the same?
    • Why did you include the PL/SQL tag? Your question has absolutely nothing to do with PL/SQL.


    Aside from that, your data (for testing) makes no sense. The exact dates you included in the time table are marked Y for weekend_flag exactly on weekday dates, and N exactly on dates that fall on Saturday or Sunday. Surely in your business the weekend days are not Monday through Friday, are they?

    I was also confused by the year column. Why is the "year" for date 15-MAR-20 equal to 2021, for example? Of course, there is no point in storing the year (which is a direct computation from the date itself), but if for some reason you do want to store it, it should make sense, and it doesn't make sense to me the way you posted it.

    Also, some of the dates in that table have the N flag for holiday_flag, which suggests that this is not a table for holidays only. Did you mean to suggest a complete calendar table, with one row per day for ALL days in the two years, and marking for each of them if it is a holiday or not, and also if it is a weekend day or not? If so, even if the idea was to only illustrate a few rows (not over 700 rows), you should at least explain that in the plain-English part of your question. The way you asked your question, we can only guess - and by definition only one guess can be right and all the other guesses will be wrong.

  • User_ZHKQ3
    User_ZHKQ3 Member Posts: 2 Green Ribbon

    Hi All,

    Thanks for all responses. Actually this is my 1st post in the forum and I’m newbie for SQL. Going forward I will make sure everything before sending the issue.

    Our main task is to count business days between dates it as to exclude the holidays and weekends, we have 2 tables Job detail table contains Job start and end date information, and time table contain weekend and holiday flag information.

    The sample data provided was randomly inserted and just to convey like wat all are the fields we are using in the query for better understanding.

    Thanks,

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    Hi, @User_ZHKQ3

    Has this question been answered?

    Whenever a question you post has been answered, please mark the thread as "Answered". At the bottom of each reply that helped you, where you see "Did this answer the question?", click on "Yes".

    Marking the thread as "Answered" can help people with similar questions, and also save time for people trying to help others.

    If the question has not been fully answered, then explain the problem. Point out where the solution Blushadow posted is wrong, and show how you figure the correct results in those places.

    BluShadow