This discussion is archived
4 Replies Latest reply: Nov 14, 2012 12:44 PM by user10939560 RSS

time slices - excercise 2

user10939560 Newbie
Currently Being Moderated
Hi guys,

again a question regarding time slices:

This time I need to correlate time slices (start / stop) of employees according to their priority (or importance):

assuming three employees approaching and leaving the office at different times I would like to get all relevant time slices with always the highest priority (importance). So if for example the boss (prio 10) is there together with an ordinary employee (prio 5 or 1), then we choose 10.

here the test data:
  CREATE TABLE "SCOTT"."EMP_START_END2" 
   (     "EMP" NUMBER(*,0), 
     "START_TIME" DATE, 
     "END_TIME" DATE, 
     "PRIO" NUMBER(2,0)
   );
REM INSERTING into SCOTT.EMP_START_END2
Insert into SCOTT.EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (10803,to_date('2012/10/05 06:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/05 16:00:00','YYYY/MM/DD HH24:MI:SS'),10);
Insert into SCOTT.EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (10802,to_date('2012/10/05 06:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/05 20:00:00','YYYY/MM/DD HH24:MI:SS'),5);
Insert into SCOTT.EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (10801,to_date('2012/10/05 08:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/06 06:00:00','YYYY/MM/DD HH24:MI:SS'),1);
expected result:
start                          end                              prio
2012/10/05 06:00:00  2012/10/05 16:00:00                          10
2012/10/05 16:00:00  2012/10/05 20:00:00                           5
2012/10/05 20:00:00  2012/10/06 06:00:00                           1
looking forward to your answers!
BR
Dirk

Edited by: user10939560 on 14.11.2012 11:21
  • 1. Re: time slices - excercise 2
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Dirk,
    user10939560 wrote:
    Hi guys,

    again a question regarding time slices:

    This time I need to correlate time slices (start / stop) of employees according to their priority (or importance):

    assuming three employees approaching and leaving the office at different times I would like to get all relevant time slices with always the highest priority (importance). So if for example the boss (prio 10) is there together with an ordinary employee (prio 5 or 1), then we choose 10.

    here the test data:
    CREATE TABLE "SCOTT"."EMP_START_END2" 
    (     "EMP" NUMBER(*,0), 
         "START_TIME" DATE, 
         "END_TIME" DATE, 
         "PRIO" NUMBER(2,0)
    );
    Don't create your own tables in Oracle-supplied schemas, such as SCOTT. Create your own schema for you osn tables.
    REM INSERTING into SCOTT.EMP_START_END2
    Insert into SCOTT.EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (10803,to_date('2012/10/05 06:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/05 16:00:00','YYYY/MM/DD HH24:MI:SS'),10);
    Insert into SCOTT.EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (10802,to_date('2012/10/05 06:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/05 20:00:00','YYYY/MM/DD HH24:MI:SS'),5);
    Insert into SCOTT.EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (10801,to_date('2012/10/05 08:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/06 06:00:00','YYYY/MM/DD HH24:MI:SS'),1);
    expected result:
    start                          end                              prio
    2012/10/05 06:00:00  2012/10/05 16:00:00                          10
    2012/10/05 16:00:00  2012/10/05 20:00:00                           5
    2012/10/05 20:00:00  2012/10/06 06:00:00                           1
    Always explain how you get the results you want.
    What does each row of output represent? Is it a continuous period with the same prio? For example, if you add another row to the sample data:
    Insert into EMP_START_END2 (EMP,START_TIME,END_TIME,PRIO) values (987,to_date('2012/10/05 05:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('2012/10/05 15:00:00','YYYY/MM/DD HH24:MI:SS'),10);
    would you want these results:
    start                          end                              prio
    2012/10/05 05:00:00  2012/10/05 16:00:00                          10
    2012/10/05 16:00:00  2012/10/05 20:00:00                           5
    2012/10/05 20:00:00  2012/10/06 06:00:00                           1
    or would you want
    start                          end                              prio
    2012/10/05 05:00:00  2012/10/05 06:00:00                          10
    2012/10/05 06:00:00  2012/10/05 16:00:00                          10
    2012/10/05 16:00:00  2012/10/05 20:00:00                           5
    2012/10/05 20:00:00  2012/10/06 06:00:00                           1
    Always say what version of Oracle you're using.
  • 2. Re: time slices - excercise 2
    user10939560 Newbie
    Currently Being Moderated
    Hi Frank,

    overlapping and bounding time slices should be merged (so version 1).

    But how do you come to

    2012/10/05 05:00:00 2012/10/05 06:00:00

    is not in the test data, is it? Did you add yourself?

    BR
    Dirk

    PS: db version: 11

    Edited by: user10939560 on 14.11.2012 12:00
  • 3. Re: time slices - excercise 2
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Dirk,
    user10939560 wrote:
    Hi Frank,

    overlapping and bounding time slices should be merged (so version 1).
    Here's one way to do that:
    WITH   got_event_start_time AS
    (
         SELECT     start_time     AS start_time
         FROM     emp_start_end2
        UNION
         SELECT     end_time     AS start_time
         FROM     emp_start_end2
    )
    ,     events          AS
    (
         SELECT     start_time
         ,     LEAD (start_time) OVER ( ORDER BY  start_time)     AS end_time
         FROM     got_event_start_time
    )
    ,     got_grp          AS
    (
         SELECT       ev.start_time
         ,       ev.end_time
         ,       MAX (ese.prio)     AS prio
         ,       ev.end_time - SUM ( ev.end_time
                                    - ev.start_time
                            ) OVER ( PARTITION BY  MAX (ese.prio)
                                        ORDER BY          ev.end_time
                                ) AS grp
         FROM       events       ev
         JOIN       emp_start_end2  ese  ON  ev.start_time  < ese.end_time
                                 AND ev.end_time       > ese.start_time
         GROUP BY  ev.start_time
         ,            ev.end_time
    )
    SELECT       MIN (start_time)     AS start_time
    ,       MAX (end_time)     AS end_time
    ,       prio
    FROM       got_grp
    GROUP BY  grp
    ,            prio
    ORDER BY  start_time
    ;
    Whenever you want to understand a long query like this, run each of the sub-queries separately, to see what they're doing.
    The first sub-query, got_event_start_time, is creating a result set of all the times when someone entered or left. Each row of the final result set will be a groupd of one or more consecutive rows from this set.
    The next sub-query, events, adds the end_time to each event.
    Got_grp is where the real work takes place. First, it joins all the overlapping rows from events and emp_start_end2 to get the highest priority present during the course of each event. Then, it uses the Fixed Difference   technique to assign an identifier (grp) that will tell if events with the same prio belong to the same group. For an explanation of fixed differences, see {message:id=9953384} and/or {message:id=9957164}
    But how do you come to

    2012/10/05 05:00:00 2012/10/05 06:00:00

    is not in the test data, is it? Did you add yourself?
    That is in the test data. Those are the start_time and end_time of the additional row I posted.

    Edited by: Frank Kulash on Nov 14, 2012 3:26 PM
    Added expanation.
  • 4. Re: time slices - excercise 2
    user10939560 Newbie
    Currently Being Moderated
    Hi Frank,

    thanks for your quick and comprehensive answer as usual :-)

    It works for me!

    I need to analyse a bit your main query and read your explaination (several times :-)) in order to understand it fully.

    BR
    Dirk

    PS: you have added the row by yourself in order to cover additional scenarios. Thanks as well for that!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points