CODEWITHSUNDEEP
sqloracle-databaseoracle11ggaps-and-islands
Mohaideen Shalim
Mohaideen Shalim

Reputation: 31

Need Oracle sql query for grouping the date

I have a table with a date from 02/11/2015 to 01/12/2015. eg... 

ATTNDATE
--------
02/11/2015
03/11/2015
--
--
--
01/12/2015.

This table may have some missing dates also.. Lets assume 06/11/2015 and 20/11/2015 is missing from this table.

I want to get an output like

SL.No      ATTNFROM          ATTNTILL
  1.       02/11/2015        05/11/2015
  2.       07/11/2015        19/11/2015
  3.       21/11/2015        01/12/2015

Kindly help me to get this output in oracle plsql

Upvotes: 3

Views: 172

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

You can do this with lead and lag analytic functions - in a subquery which you then group over, which may be what you missed - but you can also do it with an analytic 'trick'.

If you look at the difference between each date and the lowest date you get a broken sequence, in your case 0, 1, 2, 3, 5, ..., 27, 28, 29. You can see that with attndate - min(attndate) over ().

You also have another unbroken sequence available from row_number() over (order by attndate), which gives you 1, 2, 3, ... 28.

If you subtract one from the other each contiguous block of dates gets the same answer, which I've called 'slot_no':

select attndate,
  attndate - min(attndate) over ()
    - row_number() over (order by attndate) as slot_no
from your_table;

With this data every row gets either -1, 0 or 1. (You can add two to those to make them more friendly if you want, but that only really works if the gaps in the data are single days). You can then group by that slot number:

with cte as (
  select attndate,
    attndate - min(attndate) over ()
      - row_number() over (order by attndate) as slot_no
  from your_table
)
select dense_rank() over (order by slot_no) as slot_no,
  min(attndate) as attnfrom, max(attndate) as attntill
from cte
group by slot_no
order by slot_no;

With some generated data:

alter session set nls_date_format = 'DD/MM/YYYY';
with your_table (attndate) as (
  select date '2015-11-02' + level - 1 from dual connect by level <= 4
  union all select date '2015-11-07' + level - 1 from dual connect by level <= 13
  union all select date '2015-11-21' + level - 1 from dual connect by level <= 11
),
cte as (
  select attndate,
    attndate - min(attndate) over ()
      - row_number() over (order by attndate) as slot_no
  from your_table
)
select dense_rank() over (order by slot_no) as slot_no,
  min(attndate) as attnfrom, max(attndate) as attntill
from cte
group by slot_no
order by slot_no;

   SLOT_NO ATTNFROM   ATTNTILL 
---------- ---------- ----------
         1 02/11/2015 05/11/2015
         2 07/11/2015 19/11/2015
         3 21/11/2015 01/12/2015

If your real scenario is getting these ranges for multiple keys, say a person ID, then you can add a partition by clause to each of the analytic function calls, in the three over () sections.

Upvotes: 6

Related Questions