2014-04-11

Gouping consecutive dates

I just read about Tabitosan 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

No comments:

Post a Comment

About Me

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