Forum Stats

  • 3,768,656 Users
  • 2,252,827 Discussions
  • 7,874,672 Comments

Discussions

Categories

overlap Hours calculations with mandatory & optional work.

User_YL3T0
User_YL3T0 Member Posts: 8 Green Ribbon
in SQL & PL/SQL

Hi,


I am trying to calculate Total, Overlap & Net Hours for the following data.

with data as (

select 1111 as person_id, '01-AUG-11' as event_date, to_date('01-AUG-11 18:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 06:00', 'DD-MON-YY Hh24:MI') as end_time, 'AAA' project_id,'Y' as mandatory_work from dual union all

select 1111 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 03:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 04:00', 'DD-MON-YY Hh24:MI') as end_time, 'BBB' project_id ,'Y' as mandatory_work from dual union all

select 1111 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 05:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 07:15', 'DD-MON-YY Hh24:MI') as end_time, 'CCC' project_id,'N' as mandatory_work from dual union all

select 1111 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 06:16', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 07:00', 'DD-MON-YY Hh24:MI') as end_time, 'CCC' project_id,'N' as mandatory_work from dual union all


select 1111 as person_id, '03-AUG-11' as event_date, to_date('03-AUG-11 11:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 15:00', 'DD-MON-YY Hh24:MI') as end_time, 'AAA' project_id,'Y' as mandatory_work from dual union all

select 2222 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 18:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 08:00', 'DD-MON-YY Hh24:MI') as end_time, 'AAA' project_id,'Y' as mandatory_work from dual union all

select 2222 as person_id, '03-AUG-11' as event_date, to_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') as end_time, 'BBB' project_id,'Y' as mandatory_work from dual union all

select 2222 as person_id, '03-AUG-11' as event_date, to_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') as end_time, 'CCC' project_id,'Y' as mandatory_work from dual

)


--

-- end of test data

--

select

person_id, start_time, end_time, project_id,mandatory_work,

(end_time - start_time) * 24 hours,

(end_time - start_time) * 24 -greatest((

 end_time - greatest(start_time, nvl(lag(end_time) over (partition by person_id, mandatory_work order by mandatory_work DESC,start_time ASC,end_time DESC),start_time))

) * 24, 0) overlap_hrs,

greatest((

 end_time - greatest(start_time, nvl(lag(end_time) over (partition by person_id,mandatory_work order by mandatory_work DESC,start_time ASC,end_time DESC),start_time))

) * 24, 0) Net_hrs

from data

order by

person_id, mandatory_work desc, start_time, end_time

;

However, there is a twist in requirement for a specific calendar date.

  1. First overlap is calculated for person, date & Mandatory_work= "Y".
  2. Take min of start_date & max of end_date with Mandatory _work=’Y’ for given calendar date.

a.      If Mandatory _work=’N’ is between min of start_date & max of end_date with Mandatory_work=’Y’ then Net Hrs. is zero Otherwise delta between those two dates.

b.     If mandatory_work=’N’ is NOT between min of shift_start_date & max of shift_end_date with mandatory_work=’Y’ then Net Hrs. is NOT zero AND total Hrs is Net Hours & overlap Hrs. is zero.

Tagged:
· Share on TwitterShare on Facebook

Answers

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

    Hi, @User_YL3T0

    Thanks for posting the sample data. Don't forget to post the exact results you want from that sample data, and your Oracle version.

    · Share on TwitterShare on Facebook
  • User_YL3T0
    User_YL3T0 Member Posts: 8 Green Ribbon

    Oracle Version: 11gr2 Express edition

    SAMPLE_DATA.txt

    Thank you so much for your prompt response. There is a minor change in data. Please look at the attached text file for the same.

    Look at the highlighted Rows.

    1. for Person ID 1111, Since highlighted row #7 almost overlap with the first row, so Net hours shouldn't be 14.03 FOR Mandatory_work='N' but just a couple of minutes where both don't overlap.
    2. for Person ID 2222, where min start_time for Mandatory_work='Y' 02-06-2021 18:00:00 & max end_time is 03-06-2021 10:00:00 so date range for Mandatory_work='N' ( 02-06-2021 17:59:00 03-06-2021 07:49:00) almost overlap with that Hence, It should get only 1 minute as Net_hrs & Overlap should be calculated accordingly.
    3. Finally, How do I add "Available" as a project ID between rows # 3, 4.
    image.png


    Please also recommend some good training around these topics.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,521 Gold Crown

    Your code woud be SOOOO much easier to read if you formatted it instead of simply posting a hot mess of unformatted, run-on code.

    Took me less time to run it through a formatter than it took to write this paragraph. 

    WITH data 
     AS (SELECT 1111                                            AS person_id, 
                '01-AUG-11'                                     AS event_date, 
                To_date('01-AUG-11 18:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('02-AUG-11 06:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'AAA'                                           project_id, 
                'Y'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 1111                                            AS person_id, 
                '02-AUG-11'                                     AS event_date, 
                To_date('02-AUG-11 03:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('02-AUG-11 04:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'BBB'                                           project_id, 
                'Y'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 1111                                            AS person_id, 
                '02-AUG-11'                                     AS event_date, 
                To_date('02-AUG-11 05:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('02-AUG-11 07:15', 'DD-MON-YY Hh24:MI') AS end_time, 
                'CCC'                                           project_id, 
                'N'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 1111                                            AS person_id, 
                '02-AUG-11'                                     AS event_date, 
                To_date('02-AUG-11 06:16', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('02-AUG-11 07:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'CCC'                                           project_id, 
                'N'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 1111                                            AS person_id, 
                '03-AUG-11'                                     AS event_date, 
                To_date('03-AUG-11 11:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('03-AUG-11 15:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'AAA'                                           project_id, 
                'Y'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 2222                                            AS person_id, 
                '02-AUG-11'                                     AS event_date, 
                To_date('02-AUG-11 18:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('03-AUG-11 08:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'AAA'                                           project_id, 
                'Y'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 2222                                            AS person_id, 
                '03-AUG-11'                                     AS event_date, 
                To_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'BBB'                                           project_id, 
                'Y'                                             AS 
                mandatory_work 
         FROM   dual 
         UNION ALL 
         SELECT 2222                                            AS person_id, 
                '03-AUG-11'                                     AS event_date, 
                To_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') AS start_time, 
                To_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') AS end_time, 
                'CCC'                                           project_id, 
                'Y'                                             AS 
                mandatory_work 
         FROM   dual) 
-- 
-- end of test data 
-- 
SELECT person_id, 
       start_time, 
       end_time, 
       project_id, 
       mandatory_work, 
       ( end_time - start_time ) * 24                           hours, 
       ( end_time - start_time ) * 24 - Greatest(( end_time - Greatest( 
                                                   start_time, Nvl( 
                                                              Lag(end_time) 
       over ( 
         PARTITION BY person_id, mandatory_work 
         ORDER BY mandatory_work DESC, start_time 
       ASC, 
       end_time DESC), start_time 
                                         )) ) * 24, 0)          overlap_hrs, 
       Greatest(( end_time - Greatest(start_time, Nvl(Lag(end_time) 
                             over ( 
                               PARTITION BY person_id, mandatory_work 
                               ORDER BY mandatory_work DESC, 
                             start_time 
                             ASC, 
                             end_time DESC), start_time 
                                                  )) ) * 24, 0) Net_hrs 
FROM   data 
ORDER  BY person_id, 
          mandatory_work DESC, 
          start_time, 
          end_time; 
    · Share on TwitterShare on Facebook
  • User_YL3T0
    User_YL3T0 Member Posts: 8 Green Ribbon

    Thank you. I didn't know how to post a clean code.. Truly appreciate your help...

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,212 Red Diamond

    Hi, @User_YL3T0

    Please look at the attached text file for the same.

    Post everything right in the this site. Not everyone who wants to help you can or will open attachments.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond

    For formatting:

    If you select several lines from your post, a little "paragraph" symbol appears to the left of the editing window. If you click on it you will see formatting options for the fragment. If you click the double-quote you will find a few options - one is for quoting text, but another is for marking text as code. That one looks like this: </>

    If you copy code from an editor, paste it here, and then format it as code (as explained above), this website's code formatter will still destroy your alignment, etc. What I found to work better is this: First, in the editing window here (on the web site), I type two lines of nonsense, like

    aaa

    aaa

    then I format them as code. (Still nonsense, right?) Then I copy the code from my code editor, come to the editing window here, select the "nonsense" that is already formatted as code, and paste my actual code instead. I found that doing so preserves my code exactly as it was in the code editor - the web site formatter doesn't mess with it anymore.

    It takes a bit of practice and experimentation, but it's not too hard.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond

    Regarding your question (the real one) - I am trying to understand it at the abstract level.

    So, for each person id, you have several projects, and for each person and project, you have one or more time interval (or intervals) of MANDATORY work. Question 1: Is it guaranteed that the data makes sense - that is, for each person, the MANDATORY work intervals in the table don't overlap? I assume they can immediately succeed each other; so a person may work on one project until 10:00 AM, and then immediately (at 10:00 AM) start work on a different project (or perhaps continue on the same project; that could be shown more efficiently in the data, but it would not necessarily be a contradiction). Is that correct?

    Then, for each person and project, you also have time intervals marked as "NOT mandatory". What does that mean - a person was supposed to work on something from 3:30 to 4:30 (that's the MANDATORY), but they actually worked from 3:25 to 4:33 (that is the NOT MANDATORY), and you are computing the extra time they worked on it? They started 5 minutes early and ended three minutes late, so the "net" is eight minutes?

    Are the "not mandatory" intervals for a person also non-overlapping (guaranteed in the data, not part of your current task)? If not, how would you handle overlapping non-mandatory intervals for a given person, either for the same project or for different projects?

    Finally: you ask about "available" periods. What does that mean? Periods that were not assigned to MANDATORY work? Or what else? And, "available" from what start date-time and until when? What I mean is this: person 1234 must work on project AAAA from 9:00 to 11:00 AM. They are available, then, from 1 January 1900 until 31 December 9999 - except for those two hours on a given date. Very likely, that's not what you mean by "available". So, what are the "global" start and end date-times to determine "available"?

    These questions seem to have a lot to do with interval arithmetic/algebra, overlapping ranges with priority, etc. Stew Ashton has a few articles about this on his web site, for example: https://stewashton.wordpress.com/2014/03/22/overlapping-ranges-with-priority/

    · Share on TwitterShare on Facebook
  • User_YL3T0
    User_YL3T0 Member Posts: 8 Green Ribbon

    Thank you for your interest in my post. Please find details as follows:

    1.   Mandatory work doesn't overlap with the other mandatory work. However, It may overlap with Optional work. ( Mandatory work ='N').
    2.  I can continue working on my mandatory assigned project work, but I can in parallel work on my own interest some personal interest project at exactly the same time windows when I am working on mandatory work.
      1. This optional work can overlap with mandatory work & other optional work as well. (E.g. I am working on some java design documents as mandatory work but in parallel, I could listen to some podcasts on the ORACLE subquery & parallel watch a cooking show on TV with a mute volume.) Please ignore the quality of work here...
      2. What does that mean - a person was supposed to work on something from 3:30 to 4:30 (that's the MANDATORY), but they actually worked from 3:25 to 4:33 (that is the NOT MANDATORY), and you are computing the extra time they worked on it? They started 5 minutes early and ended three minutes late, so the "net" is eight minutes?
        1. Here there are two scenarios. In simple words allocate hours to mandatory work first, fill gaps with optional work wherever possible.
        2.  First I want to add “Available” in project id when there is a gap between two consecutive mandatory activities.
        3. Secondly, If optional work overlap with “Available” Project ID then allocate time to optional work as per example given allocate time to optional work between 3:30 to 4:30 as one hour since there was no mandatory work going on during that time. (Assuming that other mandatory work was in progress before 3:30 and exactly after 4:30). If optional work partially overlap with available slot & partially with mandatory work then intersection of available slot overlapping with optional slot can be considered for optional work.
    3. What does available means? Let’s assume 6 hours work window morning 6:00 to 12:00 & again from 12:00 pm to 6:00 evening and finally 6 to 12:00 mid-night.

    I am going through Stew Ashton & Alberto Dell’Era's blog to understand how this is being done.. Any help is highly appreciated..

    · Share on TwitterShare on Facebook
  • User_YL3T0
    User_YL3T0 Member Posts: 8 Green Ribbon

    Hi @mathguy ,

    I got solution for available hours #3. A Big Thanks to @Solomon Yakobson .

    reference: https://community.oracle.com/tech/developers/discussion/2252558/fill-gaps-in-dates 

    SELECT t.person_id, t.event_date,t.MAX_PREV_END_DATE AS START_TIME, T.START_TIME AS END_TIME, 'AVAILABLE' AS PROJECT_ID, t.Mandatory_work
from
(select t.*,
             max(end_time) over ( partition by person_id,mandatory_work order by start_time
                                 rows between unbounded preceding and 1 preceding
                                ) as max_prev_end_date
      from  t 
    
     ) t  WHERE max_prev_end_date < end_time 
union all 
select * from t order by person_id,mandatory_work desc, start_time asc, end_time desc  );

    Still trying to understand blog referenced for my requirement Stew Ashton & Alberto Dell’Era's blog.

    · Share on TwitterShare on Facebook