CODEWITHSUNDEEP
sqloracle-database
Devadas Wagle
Devadas Wagle

Reputation: 37

Consecutive records in oracle SQL

I have a similar question thought to post here. Need Employees who were absent for 3 or more consecutive days. Assume all the days are working days no holidays on Saturday/Sunday/Anyother days.

In the table below I need 20342123 and 20311111 in the result set. Employee 20311333 has taken 4 leaves but they are not consecutive.

Thanks for help in advance. 

Emp Id     |  AsofDate      | Comment 
----------------------------------
20342123   |  1-JAN-2017| Absent 
20342123   |  2-JAN-2017| Absent 
20342123   |  3-JAN-2017| Absent
20311111   |  1-JAN-2017| Absent 
20311111   |  2-JAN-2017| Absent 
20311111   |  3-JAN-2017| Absent
20311333   |  5-JAN-2017| Absent
20311333   |  6-JAN-2017| Absent
20311333   |  8-JAN-2017| Absent 
20311333   |  9-JAN-2017| Absent
20322222   |  1-JAN-2017| Absent
20322222   |  2-JAN-2017| Absent

Upvotes: 0

Views: 1020

Answers (2)

arushi singh
arushi singh

Reputation: 1

select emp_id,AsofDate,Comment FROM ( SELECT emp_id,AsofDate,Comment, lag(AsofDate) over(partition by emp_id order by AsofDate)prev_date, lag(AsofDate,2) over(partition by emp_id order by AsofDate)prev_pre_date from Employees_data where Comment = 'Absent') WHERE prev_date=AsofDate-1 and prev_pre_date = AsofDate-2;

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Although you can apply a "gaps-and-islands" solution, I think lag()/lead() is simpler for this problem:

select distinct emp_id
from (select t.*,
             lead(date) over (partition by empid order by date) as date_1,
             lead(date, 2) over (partition by empid order by date) as date_2
      from t
      where comment = 'Absent'  -- not sure if this is important
     ) t
where date_1 = date + 1 and date_2 = date + 2;

Upvotes: 1

Related Questions