Find Users who worked for 5 consecutive days with

问题:

I have a table has data 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 list of employees between a date range(say for the last 3 months) who worked for code W conescutively for 5 days with the date range in the output. Each employee can have multiple records for a single day with different codes.

Expected Output is

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

Below is what I tried but I didn't come close at all to the output I seek

 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 worked for 10 consecutive days, he should be listed twice in the output with both date ranges. If E1 has worked for 9 days consecutively(11/1 to 11/9), he should be listed only once with the date range 11/1 to 11/9.

I have already seen questions which are similar but none of them exactly worked out for me. My database is Oracle 10G and no PL/SQL.

回答1:

I'm not sure I understood everything correctly, but something like this might get you started:

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

Note that I used workdate instead of the date as it is a bad idea to use a reserved word as a column name.

回答2:

You may start from here:

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';

those rows with days_worked_last_5_days=5 are what you search.

See this fiddle.

回答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

Where table example is :

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

回答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;