SQL Consecutive Days - Oracle

Question

[Data]

[Emp] [Emp_group] [Date_purchase]
1      001         12-jan-2016
1      001         13-jan-2016
1      001         19-jan-2016
1      003         14-jan-2016
2      004         21-feb-2016
2      004         22-feb-2016
2      004         23-feb-2016
3      005         01-apr-2016

Need SQL to find consecutive purchase dates. Emp (1) of emp group (001) has purchased consecutively on 12 and 13 of January. Emp and Emp group partition must be considered.

What results do you want? – Gordon Linoff Jul 27 '18 at 22:42 — Gordon Linoff, Jul 27 '18 at 22:42

score 0 · Accepted Answer · answered Jul 27 '18 at 22:43

Just use lag()/lead():

select t.*
from (select t.*,
             lag(date_purchase) over (partition by emp, emp_group order by date_purchase) as prev_dp,
             lead(date_purchase) over (partition by emp, emp_group order by date_purchase) as next_dp
      from t
     ) t
where date_purchase in (prev_dp, next_dp);

SQL Consecutive Days - Oracle

1 Answers1