Grouping date’s as 1 count if difference is 5 days

oracle

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 5 as there are 5 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

Best Answer

If I understand your question, the following is the query that you are looking for.

SQL> select * from mytest;

        ID DT
---------- ---------
        22 15-MAY-17
        22 15-MAY-17
        22 15-MAY-17
        22 16-MAY-17
        22 22-MAY-17
        23 23-MAY-17
        23 25-MAY-17
        23 30-MAY-17

8 rows selected.

SQL> select id, count(diff)+1 count 
     from( 
          select id, dt - lag(dt) over(order by dt) diff 
          from mytest
          ) 
     where diff >= 5 
     group by id;

        ID         COUNT
---------- -------------
        22             2
        23             2

Here is the fiddle.

For more about LAG()

Documentation: LAG

Related Question