CODEWITHSUNDEEP
sqloracle-database
Sandy
Sandy

Reputation: 27

Oracle SQL - Find Consecutive Values on Consecutive Dates

I have to write a query to find 3 or more consecutive occurrence of a value (Refer Column Flag) and it must happen on consecutive dates.The query should not pick values if the occurrence doesn't happen on consecutive dates For e.g

COLUMN ID   DATE            FLAG
100         10-JUL-2015     Y
100         11-JUL-2015     Y
100         12-JUL-2015     Y
100         13-JUL-2015     N
100         14-JUL-2015     Y
100         15-JUL-2015     Y
100         16-JUL-2015     N
100         17-JUL-2015     Y
100         18-JUL-2015     Y
100         19-JUL-2015     Y
100         20-JUL-2015     Y
100         21-JUL-2015     Y

OUTPUT

COLUMN ID   DATE            FLAG
100         10-JUL-2015     Y
100         11-JUL-2015     Y
100         12-JUL-2015     Y
100         17-JUL-2015     Y
100         18-JUL-2015     Y
100         19-JUL-2015     Y
100         20-JUL-2015     Y
100         21-JUL-2015     Y

Any Idea to accomplish this in Oracle SQL. I am trying to use analytic functions like LAG and LEAD but unable to accomplish this.

Upvotes: 1

Views: 5716

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can try this. This makes use of recursive common table expressions and lead analytical function.

with x as
(select id, mydate, flag from table1 where flag = 'Y')
, y as(select id, mydate, lead(mydate) over(order by mydate) as nxt,flag from x)
, z as (select id, mydate, nxt, lead(nxt) over(order by nxt) as nxt_1,flag from y)
select distinct t.id, t.mydate,t.flag from z
join x t on z.id = t.id 
and (t.mydate = z.mydate or t.mydate = z.nxt or t.mydate = z.nxt_1)
where z.nxt-z.mydate = 1 and z.nxt_1-z.nxt =1
order by t.mydate

SQLfiddle with test data : http://sqlfiddle.com/#!4/9bbed/1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this with a very handy trick. The groups of consecutive values can be calculated using a difference of row_number()s. Then, you need to get the count for each group and select the ones that match your condition:

select t.*
from (select t.*, count(*) over (partition by id, flag, grp) as cnt
      from (select t.*,
                   (row_number() over (partition by id order by date) -
                    row_number() over (partition by id, flag order by date)
                   ) as grp
            from table t
           ) t
     ) t
where cnt >= 3;

Strictly speaking, you do not need the difference of row_numbers(). Assuming your dates have no time components, the following will also suffice:

select t.*
from (select t.*, count(*) over (partition by id, flag, grp) as cnt
      from (select t.*,
                   (date -
                    row_number() over (partition by id, flag order by date)
                   ) as grp
            from table t
           ) t
     ) t
where cnt >= 3;

Upvotes: 3

Related Questions