Find users who have been working for 5 consecutive days with a range of output dates

Question

I have data in the table similar to below

  Emp  Date        Code     
  ---  --------    ---- 
  E1  11/1/2012    W 
  E1  11/1/2012    V   
  E2  11/1/2012    W   
  E1  11/2/2012    W
  E1  11/3/2012    W
  E1  11/4/2012    W
  E1  11/5/2012    W

I want to get a list of employees between a date range (say, over the last 3 months) that has been working on the W code conditionally for 5 days with a date range in the output. Each employee can have several records in one day with different codes.

Expected Result

Emp   Date-Range 
---   ----------
 E1   11/1 -11/5

Below I tried, but I did not come close to the exit that I was looking for

 SELECT distinct user, MIN(date) startdate, MAX(date) enddate
FROM (SELECT user, date, (TRUNC(date) - ROWNUM) tmpcol
      FROM (SELECT user, date
              FROM tablename
             where date between to_date('10/01/2012','mm/dd/yyyy') and to_date('10/03/2012','mm/dd/yyyy')
             ORDER BY user, date) ot) t
 GROUP BY user, tmpcol
 ORDER BY user, startdate;

If Emp E1 has been running for 10 consecutive days, it should be listed twice in the output with both date ranges. If E1 has been working for 9 consecutive days (from 11/1 to 11/9), it should be indicated only once with a date range from 11/1 to 11/9.

, . - Oracle 10G PL/SQL.

Answer 1

, , - :

select emp, 
       sum(diff) as days,
       to_char(min(workdate), 'yyyy-mm-dd') as work_start,
       to_char(max(workdate), 'yyyy-mm-dd') as work_end
from (       
  select *
  from (
    select emp, 
           workdate, 
           code, 
           nvl(workdate - lag(workdate) over (partition by emp, code order by workdate),1) as diff
    from tablename
    where code = 'W'
     and workdate between ...
  ) t1
  where diff = 1 -- only consecutive rows
) t2
group by emp
having sum(diff) = 5

SQLFiddle: http://sqlfiddle.com/#!4/ad7ae/3

, workdate date, .

Answer 2

:

select 
emp, count(*) over (partition by emp, code order by date_worked range interval '5' day preceding) as days_worked_last_5_days
from table
where code='W';

days_worked_last_5_days = 5 - , .

.

Answer 3

Select emp, data-5, data from (SELECT EMP, DATA, WORK,lag, lead, row_number() over(PARTITION BY emp--, DATA 
ORDER BY DATA asc) rn
  FROM (SELECT emp,
               data,
               work,
               LAG (data) OVER (PARTITION BY emp ORDER BY data ASC) LAG,
               LEAD (data) OVER (PARTITION BY emp ORDER BY data ASC) LEAD
          FROM (SELECT emp,
                       data,
                       work,
                       ROW_NUMBER ()
                           OVER (PARTITION BY emp, data ORDER BY data ASC)
                           rn
                  FROM example)
         WHERE rn = 1) a
WHERE a.data + 1 = LEAD AND a.data - 1 = LAG
) WHERE rn = 5

:

EMP (varchar2), , 'W' 'F'

Answer 4

SELECT * FROM (
SELECT USERID,USEDATE,WRK,RANK() OVER (PARTITION BY USERID,WRK ORDER BY USEDATE ) AS RNK1 FROM USER1  )U1 JOIN
(
SELECT USERID,USEDATE,WRK,RANK() OVER (PARTITION BY USERID,WRK ORDER BY USEDATE ) AS RNK2 FROM USER1  )U2 ON U1.USERID=U2.USERID AND U1.RNK1+3=U2.RNK2 AND U2.USEDATE-U1.USEDATE=3;