SELECTing trainee peer history Oracle

Question

I have a table T1 with empid, shiftdate and peerid. This stores history of when an employee trained someone. For example, for employee ABCD this is the data I have:

EMPLOYEE|SHIFT_DATE|PEERID  
ABCD 05/10/2013 A123  
ABCD 05/09/2013 A123  
ABCD 05/08/2013 A123  
ABCD 05/07/2013 X456  
ABCD 05/06/2013 X456  
ABCD 05/05/2013 X456  
ABCD 05/04/2013 A123  
ABCD 05/03/2013 A123  
ABCD 05/02/2013 A123  
ABCD 05/01/2013 A123

I want to select employee, trainee and trained_since (date). As from the data above some trainees can be repeated over different non-contiguous date/period blocks, so each data period needs to treated as a separate training period, like:

EMPLOYEE|TRAINED_SINCE|TRAINEE  
ABCD 05/08/2013 A123  
ABCD 05/05/2013 X456  
ABCD 05/01/2013 A123

How do I do this? please help.

Sounds like top one from each category. What if a trainee has more than one trainer? [top record from each group](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — जलजनक, May 24 '13 at 14:55
So is it just the earliest date for each employee / trainee pair you want, or if the dates were non-contiguous would you want the earliest from both periods, or something else? — Rup, May 24 '13 at 14:56
SparKot - Trainee can have only one employee assigned to him per shiftdate. — ozzboy, May 24 '13 at 15:33

kiwiwings · Answer 1 · 2013-05-24T16:22:39.747

1

well ... just a few seconds too late :(

    select * from (
            select employee
              , decode(lag(peerid,1,'') over (partition by employee order by shift_date), peerid, null, shift_date)  trained_since
              , peerid
            from abe_trainee
    )
    where trained_since is not null

edited May 24 '13 at 16:22

answered May 24 '13 at 16:16

kiwiwings

3,386
1
21
57

score 0 · Accepted Answer · answered May 24 '13 at 16:14

Use LAG to define boundaries:

SQL> SELECT employee, shift_date, peer_id
  2         CASE
  3            WHEN peer_id = lag(peer_id)
  4                           over(PARTITION BY employee ORDER BY shift_date)
  5            THEN
  6             0
  7            ELSE
  8             1
  9         END gap
 10    FROM DATA;

EMPLOYEE     SHIFT_DATE  PEER_ID             GAP
------------ ----------- ------------ ----------
ABCD         01/05/2013  A123                  1
ABCD         02/05/2013  A123                  0
ABCD         03/05/2013  A123                  0
ABCD         04/05/2013  A123                  0
ABCD         05/05/2013  X456                  1
ABCD         06/05/2013  X456                  0
ABCD         07/05/2013  X456                  0
ABCD         08/05/2013  A123                  1
ABCD         09/05/2013  A123                  0
ABCD         10/05/2013  A123                  0

Use an outer query to retrieve those rows:

SQL> SELECT employee, shift_date since, peer_id
  2    FROM (SELECT employee, shift_date, peer_id,
  3                 CASE
  4                    WHEN peer_id = lag(peer_id)
  5                                   over(PARTITION BY employee
  6                                        ORDER BY shift_date)
  7                    THEN
  8                     0
  9                    ELSE
 10                     1
 11                 END gap
 12         FROM DATA)
 13  WHERE gap = 1;

EMPLOYEE     SINCE       PEER_ID
------------ ----------- ------------
ABCD         01/05/2013  A123
ABCD         05/05/2013  X456
ABCD         08/05/2013  A123

SELECTing trainee peer history Oracle

2 Answers2