Search code examples
sqloraclelagrankpartition

SQL Consecutive Days - Oracle

[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.

Solution

  • 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);