Forum Stats

  • 3,767,862 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

Work Hrs and Mins between dates

Newuser1
Newuser1 Member Posts: 280 Blue Ribbon

Hello All,

We have target table with Date_1 and Date_2 and Calendar table with time details along with Weekend_flg and Holiday_flg.

We are trying to achieve Working Hrs and Mins without weekends and public holidays by making use of both tables business Hrs we have to consider 8 AM to 5 PM. If any work done before or after these Working Hrs we should not consider those Hrs Mins.

We used below Query to find the working Hrs, issue we are facing is whenever the Total_diff is 0 or 1 day it’s not working properly if Total_diff is more than 1 days its working correctly below is the query we used.

Can someone provide the inputs for these is much appreciated.

######

WITH DAYS AS (select distinct id,Date_1,Date_2,Total_Diff,Weekend_total,Holiday_total,

(Total_Diff - (WEEKEND_total+HOLIDAY_total)) AS work_DAYS

from

(select distinct id,Date_1,Date_2,Total_Diff,Weekend_total,sum(holiday_flag) over (partition BY ID) AS Holiday_total

from 

(SELECT distinct A.task_pyid,A.Date_1,a.Date_2,

(round(to_date(Date_2, 'YYYY-MM-DD hh24:mi:ss')-TO_DATE(Date_1,'YYYY-MM-DD hh24:mi:ss'))

as Total_DIFF,

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

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

+((trunc(Date_2,'IW')-trunc(Date_1,'IW'))/7-1)*2 as Weekend_total,

case when b.holiday_flg='Y' then 1 

   when b.holiday_flg='N' then 0 

   else 0 end as holiday_flg

from target A , Calendar B

where B.time_key between A.Date_1 and A.Date_2

SELECT distinct A.id,A.Date_1,a.Date_2,Total_DIFF,Weekend_total,Holiday_total,work_DAYS,

 Working_DAYS*9+( CASE WHEN (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))-8) >=9 then 9

 ELSE (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))-8) END) as Work_HRS,

 case when (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))) >=17 then 0 

 else (TO_NUMBER(TO_CHAR(A.Date_2,'mi'))) end as work_MIN

 from target A ,calender B,DAYS D

where b.time_key between A.Date_1 and a.Date_2

#########

Tagged:

Best Answer

Answers

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

    Hi, @Raa.BI

    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 you get those results from that data.

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

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

    Hi, @Raa.BI

    Is the query you posted what you're actually running? The 7th-8th lines from the end are:

    where B.time_key between A.Date_1 and A.Date_2
    SELECT distinct A.id,A.Date_1,a.Date_2,Total_DIFF,Weekend_total,Holiday_total,work_DAYS,
    

    which looks like an error. Also, it seems to have more left '('s than right ')'s.

  • mathguy
    mathguy Member Posts: 10,144 Blue Diamond

    Do you need help fixing your existing code?

    Or will you accept a completely different solution, which is likely to be easier to maintain, does not require a calendar table, and is likely to be more efficient? An elementary direct computation, using just a small table listing the holidays, should suffice.

    Two important questions:

    1. What is your database version? If you don't know, run select * from v$version and see what it reports. A number like 11.2.0.4 or 12.2.0.1.
    2. I see that you are using the (undocumented?) nls_language = ''numeric date language'''. The value returned depends on nls_territory.Does this mean that you want your query to work correctly in different territories, where "weekend" may mean different things? In any case, note that in that "language" the day number of Monday is 1 in America and 0 in Europe (for example), but in both places weekend means Saturday and Sunday; accounting for "weekends" using that nls_language is definitely wrong. But the question still stands: Do you need this to work with different definitions of "weekend"? If so, where do you find those definitions? And do the holidays also depend on territory?


  • Newuser1
    Newuser1 Member Posts: 280 Blue Ribbon

    Hi,

    Thank you for your response.

    This is my first post under SQL forum, going forward I will make sure everything. The below query is the working one and version we are using Oracle database 12.1.0.2.0.

    Here we calculated weekend total by making use of Target table, and for holiday total we have calendar table with holiday flag (‘Y’ and ‘N’) so by using this flag field we calculated holiday total.

    Looking help for fix in existing code or with new solution/approach also accepted. 

    WITH DAYS AS (select distinct id,Date_1,Date_2,Total_Diff,Weekend_total,Holiday_total,

    (Total_Diff - (Weekend_total+Holiday_total)) AS work_Days

    from

    (select distinct id,Date_1,Date_2,Total_Diff,Weekend_total,sum(holiday_flag) over (partition BY id) AS Holiday_total

    from 

    (SELECT distinct A.id,A.Date_1,a.Date_2,

    round(to_date(Date_2, 'YYYY-MM-DD hh24:mi:ss')-TO_DATE(Date_1,'YYYY-MM-DD hh24:mi:ss'))

    as Total_DIFF,

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

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

    +((trunc(Date_2,'IW')-trunc(Date_1,'IW'))/7-1)*2 as Weekend_total,

    case when b.holiday_flag='Y' then 1 

       when b.holiday_flag='N' then 0 

       else 0 end as holiday_flag

    from target A , Calendar B

    where B.time_key between A.Date_1 and A.Date_2)))

    SELECT distinct A.id,A.Date_1,A.Date_2,Total_DIFF,Weekend_total,Holiday_total,work_Days,

     Work_Days*9+( CASE WHEN (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))-8) >=9 then 9

     ELSE (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))-8) END) as Work_HRS,

     case when (TO_NUMBER(TO_CHAR(A.Date_2,'HH24'))) >=17 then 0 

     else (TO_NUMBER(TO_CHAR(A.Date_2,'mi'))) end as work_MIN

    from target A ,calendar B,DAYS D

    where b.time_key between A.Date_1 and a.Date_2

    and D.id=A.id


    Thanks,

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

    Hi, @Newuser1

    Thanks for posting the query. Don't forget to post your database version and CREATE TABLE and INSERT statements for the sample data. You may want to change the sample data so it uses more of the same dates; then you won't need to post so much data for the calendar table. Whatever sample data you use, make sure the desired results you post are really what you want from the given sample data, and explain how you get those results. For example, when counting days, do you count both date_1 and date_2?

  • mathguy
    mathguy Member Posts: 10,144 Blue Diamond

    Your computations, which I didn't follow in detail, seem to consider Saturday and Sunday as "weekend". Is that the case? You forgot to answer that question.

    And is the holiday flag a "fixed" column, regardless of who runs the query (assuming it might be run in Buenos Aires, Cairo or Mumbay)? Or is everything in one place (one single country)?

    Do you need the output as you show it, or just the final answer - "so many hours and minutes worked"?

  • Newuser1
    Newuser1 Member Posts: 280 Blue Ribbon

    Yes, Saturday and Sunday are weekends in our case and holiday_flag is fixed column with flag as ‘Y’ and ‘N’ (its US holiday list ) the output which we shared was we are getting from the above query which was showing wrong data for Total_diff is 0. 

  • mathguy
    mathguy Member Posts: 10,144 Blue Diamond

    Your query produced incorrect results in (almost) all cases, not only in the last two rows (when "days" are 0 or 1).

    Let's leave aside the first row, which is almost correct; the mistake there is that 15 February 2021 was a federal holiday in the U.S., and you said those are the holidays in your "calendar" table. So the first employee worked four work days (Friday and then Monday, Tuesday, Wednesday, all day) - but Monday was Presidents' Day and you said that shouldn't be counted. So the correct result in that case is 27 hours worked, not 36. But let's assume that the calendar table doesn't show 15 February as a holiday.

    In the second row, the correct answer is 30 hours and 26 minutes; your table shows 29 hours and 26 minutes. The employee worked three full days, which is 27 hours, and on the last day he worked from 8:00 to 11:26, that is 3:26 hours. Plus 27 hours equals 30:26, not 29:26.

    Same mistake in the third row. The correct answer is 17 hours and 21 minutes. You show 16:21. The guy worked nine hours (full day) the first day and 8:21 (from 8:00 to 16:21) on the second day, total 17:21, not 16:21.

    Fourth row, your computation shows 8 hours. The guy worked from 08:01 one day till the next morning (before 08:00); so he only gets credit for the first day. On that first day, he worked almost the full 9 hours - he arrived one minute late, so he worked 8:59, not 8:00 hours.

    I won't even touch the code, which seems wrong (or, rather more likely, it may have been correct for someone else's problem; you copied it without understanding it, and without - then - knowing what changes must be made to adapt it to your situation).

    Here is some data I created for testing. Note that in the "calendar" table I added a non-official holiday (Mother's Day - which should be made a formal holiday!) - just so I can test with holidays that fall on a weekend (Sunday in this case), to make sure the computation is still correct.

    I added a few extra rows for more testing - work beginning or ending on a holiday, including on a holiday that falls on a Sunday, to make sure the query works correctly.

    (Although, given what you said and thought, that your existing query works "correctly" except in a few cases, it seems I have already done a lot more testing than you ever did.)

    TEST DATA:

    drop table target purge;
    alter session set nls_date_format = 'dd-mm-yyyy hh24:mi';
    create table target (id, date_1, date_2) as 
      select 1, to_date('12-02-2021 07:01'), to_date('17-02-2021 17:53') from dual union all
      select 2, to_date('27-01-2021 07:01'), to_date('01-02-2021 11:26') from dual union all
      select 3, to_date('19-01-2021 07:01'), to_date('20-01-2021 16:21') from dual union all
      select 4, to_date('05-04-2021 08:01'), to_date('06-04-2021 07:09') from dual union all
      select 5, to_date('10-01-2021 20:02'), to_date('11-01-2021 03:57') from dual union all
      select 6, to_date('01-01-2021 11:20'), to_date('09-05-2021 16:30') from dual union all
      select 7, to_date('02-01-2021 14:45'), to_date('18-01-2021 11:40') from dual
    ;
    
    drop table holidays purge;
    alter session set nls_date_format='dd-mm-yyyy';
    create table holidays (dt) as 
      select to_date('01-01-2021') from dual union all
      select to_date('18-01-2021') from dual union all
      select to_date('15-02-2021') from dual union all
      select to_date('09-05-2021') from dual union all
      select to_date('31-05-2021') from dual
    ;
    


    QUERY AND OUTPUT:

     alter session set nls_date_format = 'DY dd-mm-yyyy hh24:mi';
    
    with
     h (dt) as (
        select dt
        from   holidays
        where  dt - trunc(dt, 'iw') <= 4
      )
    , first_pass (id, date_1, date_2, h1, h2, h_count) as (
       select t.id, t.date_1, t.date_2, l.h1, l.h2, l.h_count
       from   target t cross join lateral
              ( select count(case when h.dt = trunc(t.date_1) then 1 end) as h1,
                       count(case when h.dt = trunc(t.date_2) then 1 end) as h2,
                       count(case when h.dt != trunc(t.date_2) then 1 end) as h_count
                from   h
                where  h.dt between trunc(t.date_1) and trunc(t.date_2)
              ) l
      )
    , comps (id, date_1, date_2, minutes_worked) as (
        select id, date_1, date_2,
               round( 24 * 60 *
               ( case when h2 = 0 and trunc(date_2) - trunc(date_2, 'iw') <= 4
                      then least(9/24, greatest(0, date_2 - trunc(date_2) - 8/24)) else 0 end
               - case when h1 = 0 and trunc(date_1) - trunc(date_1, 'iw') <= 4
                      then least(9/24, greatest(0, date_1 - trunc(date_1) - 8/24)) else 0 end
               ))
               + round (9 * 60 * 
                 ( least(5, trunc(date_2) - trunc(date_2, 'iw'))
                 - least(5, trunc(date_1) - trunc(date_1, 'iw'))
                 + 5 * (trunc(date_2, 'iw') - trunc(date_1, 'iw')) / 7
                 - h_count
               ))
        from   first_pass
      )
    select id, date_1, date_2, trunc(minutes_worked/60) as hours, mod(minutes_worked, 60) as minutes
    from   comps
    ;
    
      ID DATE_1               DATE_2                 HOURS MINUTES
    ---- -------------------- -------------------- ------- -------
       1 FRI 12-02-2021 07:01 WED 17-02-2021 17:53      27       0
       2 WED 27-01-2021 07:01 MON 01-02-2021 11:26      30      26
       3 TUE 19-01-2021 07:01 WED 20-01-2021 16:21      17      21
       4 MON 05-04-2021 08:01 TUE 06-04-2021 07:09       8      59
       5 SUN 10-01-2021 20:02 MON 11-01-2021 03:57       0       0
       6 FRI 01-01-2021 11:20 SUN 09-05-2021 16:30     792       0
       7 SAT 02-01-2021 14:45 MON 18-01-2021 11:40      90       0
    
    User_WI23P
  • mathguy
    mathguy Member Posts: 10,144 Blue Diamond
    Accepted Answer