2014-04-11

Gouping consecutive dates

I just read about Tabibitosan method on Oracle PL/SQL forum.
Rob Van Wijk has written also about the method recently.

Here is just another example of it.
Something happening on random times. Need to know sequences of dates. Need to get a grouping information on rows, if there exist rows on yesterday or tomorrow.

with dates as (
select TIMESTAMP'2014-04-15 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-16 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 03:00:00' dt from dual
)
select dt,TRUNC(dt)-DENSE_RANK()over(order by trunc(dt)) grp
from dates
;

2014-04-15 01:00:00    2014-04-14
2014-04-15 02:00:00    2014-04-14
2014-04-15 03:00:00    2014-04-14
2014-04-16 01:00:00    2014-04-14
2014-04-17 02:00:00    2014-04-14
2014-04-17 03:00:00    2014-04-14
2014-04-19 01:00:00    2014-04-15
2014-04-19 02:00:00    2014-04-15
2014-04-21 02:00:00    2014-04-16
2014-04-21 03:00:00    2014-04-16

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.