#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2017
    Posts
    1
    Rep Power
    0

    Smile Overlapping time


    Hello
    I have 2 tables with workers and supervisor data I need the output as any aggregation data of hours spent by
    supervisor with worker. There will be overlap of time but we need to consider only the time he spent not overlap as
    additional time spent. This needs be done for all the supervisors in the table dynamically.

    Worker Working hours with supervisor


    Supervisor | Worker | Start Time | End time
    ----------------------------------------------------------------------------------------------------------------
    Z8956 | Mike | 10:33:40 AM | 4:34:23 PM


    Z8956 | Robin | 2:52:03 PM | 3:29:22 PM


    Z8956 | Riche | 3:26:55 PM | 4:34:23 PM


    D6635 | Sam | 12:38:46 PM | 1:00:27 PM


    D6635 | Jeff | 8:38:23 AM | 9:51:26 AM


    D6635 | Joe | 7:56:54 AM | 8:29:31 AM




    supervisor working hours

    Supervisor | Start Time | End time
    --------------------------------------------------------------------------------------------------------------------------
    Z8956 | 10:00:00am | 5:00:00 PM


    D6635 | 7:00:00am | 2:00:00 PM

    Output should be as below:
    This should not consider the overlap time

    Supervisor | Total Hours Spent with worker | Total Hours supervisor is free
    ----------------------------------------------------------------------------------------
    Z8956 | 180mins | 200mins
    D6635 | 200mins | 500mins

    Any help greatly appreciated
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    i don't have an answer for you... that's quite an interesting and difficult problem

    let me use this overlap diagram --

    Code:
                  09:00      17:00             
                    |          |
                    |          |
    1.  S------E    |          |
                    |          |
                    |          |
    2.          S------E       |
                    |          |
                    |          |
    3.              | S------E |
                    |          |
                    |          |
    4.          S----------------------E
                    |          |
                    |          |
    5.              |      S------E
                    |          |
                    |          |
    6.              |          |    S------E
    
    S=start_time   E=end_time
    to look at the overlaps between 09:00 and 17:00, you want to eliminate cases 1 and 6

    this is the SQL you need for that

    Code:
    WHERE end_time   >= '09:00'   /* eliminates case 1 */
      AND start_time <= '17:00'   /* eliminates case 6 */
    however, now the fun begins

    if i understand your requirements, because of case 4, "Total Hours Spent with worker" will by 8.0, while "Total Hours supervisor is free" will be zero, regardless of cases 2, 3, and 5

    if case 4 were excluded, then you still have 100% time with worker because they overlap

    this is a tricky problem indeed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    382
    Can a supervisor have more than one session with a given worker?

    Something to think about:

    A supervisor's hours of availability could be represented in table1 as follows:

    Code:
    Z8956 | Z8956 | 00:00:00 | 10:00:00
    Z8956 | Z8956 | 17:00:00 | 23:59:59
    D6635 | D6635 | 00:00:00 | 07:00:00 
    D6635 | D6635 | 14:00:00 | 23:59:59
    Last edited by cafelatte; June 2nd, 2017 at 02:00 PM.

IMN logo majestic logo threadwatch logo seochat tools logo