查找连续5天工作并输出日期范围的用户 [英] Find Users who worked for 5 consecutive days with date-range in output

问题描述

我有一个表有类似于下面的数据

 职业日期代码
 --- ---- ---- ---- 
 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 
  

我希望获得连续5天为代码W工作的日期范围（比如最近3个月）和输出中的日期范围之间的员工列表。每个员工可以在一天中使用不同的代码记录多个记录。

预期产出

 雇佣日期范围
 --- ---------- 
 E1 11/1 -11/5 
  

以下是我尝试过的，但我根本没有接近我寻求的输出

  SELECT不同用户，MIN（日期）startdate，MAX（日期）enddate 
 FROM（SELECT用户，日期，（TRUNC（日期） -  ROWNUM）tmpcol 
 FROM（SELECT user，date 
 FROM tablename 
）to_date（'10 / 01/2012'，'mm / dd / yyyy'）和to_date（'10 / 03/2012'，'mm / dd / yyyy'）
 ORDER BY user，date）ot）t 
 GROUP BY用户，tmpcol 
 ORDER BY用户，startdate; 
  

如果Emp E1连续工作了10天，他应该在输出中列出两次，范围。如果E1已连续工作9天（11/1至11/9），则应仅在11/1至11/9的日期范围内列出一次。 $ b

我已经看到类似的问题，但没有一个对我来说完全适用。我的数据库是Oracle 10G，没有PL / SQL。

解决方案

我不确定我是否正确地理解了所有内容，但是像这样可能会让你开始：

pre $ 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 $ b $ from（
select *
from（
select emp，
workdate，
code，
nvl（workdate - lag（workdate）over（由emp分区，按工作日顺序排列），1）as diff
from tablename
where code ='W'
and workdate between ...
）t1
where diff = 1 - 仅连续行
）t2
group by emp
sum（diff）= 5


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

请注意，我使用了 workdate 而不是 date ，因为使用保留字作为列名是个不错的主意。

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.

解决方案

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.

这篇关于查找连续5天工作并输出日期范围的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！