我有一个表有类似于下面的数据
职业日期代码 --- ---- ---- ---- 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的日期范围内列出一次。 我已经看到类似的问题，但没有一个对我来说完全适用。我的数据库是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 ： sqlfiddle/#!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 WI 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/5Below 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) = 5SQLFiddle: sqlfiddle/#!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天工作并输出日期范围的用户
发布评论