4 Replies Latest reply: Nov 14, 2012 2:44 PM by user10939560 RSS

    time slices - excercise 2

    user10939560
      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
          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
            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
              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
                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!