Tag Info

Hot answers tagged

2

Here is one solution: with t as ( select grp, emp_id, time_type, min(work_dt) as start_dt , max(work_dt) as end_dt from ( select row_number() over (partition by emp_id order by work_dt) - row_number() over (partition by emp_id, time_type order by ...


2

Not sure about the efficient part, but the following does the trick. You'll want (emp_id, work_dt) indexed at least, having the time_type in there should not hurt either. Assuming this table: create table times(emp_id number, work_dt date, time_type number); You can run this: (SQL Fiddle) -- get the last work date with last_dt as ( select emp_id, ...



Only top voted, non community-wiki answers of a minimum length are eligible