Merge Dates into continous date

Question

|Resource ID | Start Date | End Date|
-------------------------------------
|24565865    | 04-01-16   | 29-01-16|
|24565865    | 29-01-16   | 01-02-16|    
|24565865    | 01-02-16   | 25-03-16| 
|24565865    | 25-03-16   | 01-04-16|
|24565865    | 01-04-16   | 09-05-16|
|24565865    | 09-05-16   | 13-05-16|
|24565865    | 13-05-16   | 25-07-16|
|24565865    | 25-07-16   | 01-08-16|
|24565865    | 01-08-16   | 12-12-99|

wants to display these dates like

|Resource ID | Start Date | End Date|
-------------------------------------
|24565865    |04-01-2016  |25-03-2016|
|24565865    |01-04-2016  |12-12-2099|

.

SELECT 
    o.asset_id,o.path,RB.RESOURCE_ID,rl.start_date,rl.end_date
    ,o.resourcemanager_id,rl.resourcemanager_id 
FROM 
    objectbase o 
JOIN 
    resourcelock rl 
ON
    o.resourcemanager_id=rl.resourcemanager_id
JOIN 
    resourcebase rb 
ON
    rb.resource_id=O.ASSET_ID
WHERE 
    RB.RESOURCE_ID=24565865 AND O.CODE LIKE '186' 
ORDER BY 
    RL.START_DATE;

above is the query

What have you tried already? What, exactly, are you stuck on? What's your logic here, why the grouping that you've shown? — Rich Benner, Jun 22 '16 at 09:10
these are the start and end date and i want to show client only one start and end date for a continuous period — Akash, Jun 22 '16 at 09:11
start and end date of what? this data is a continuous list of dates, there's no gaps in there. I suggest you edit the question to give us a lot more information, there's nothing at all to work on here. — Rich Benner, Jun 22 '16 at 09:12
Are you sure that `25-03-16 to 01-04-16` should be there? Did you add that by mistake? — Utsav, Jun 22 '16 at 09:16
Is there any function or method by which we can join these dates — Akash, Jun 22 '16 at 09:24
Why do you need the date ranges 04-01-2016 to 25-03-2016 and 01-04-2016 to 12-12-2099, given that the row with start_date = 25-03-2016 and end_date = 01-04-2016 is present in the sample data? Surely that input data would give you a single range of 04-01-2016 to 12/12/2099? — Boneist, Jun 22 '16 at 10:25

score 0 · Accepted Answer · answered Jun 22 '16 at 09:42

You can use a combination of the LAG(), LEAD() and LAST_VALUE() analytic functions:

SELECT *
FROM   (
  SELECT resource_id,
         CASE
         WHEN end_date IS NOT NULL
         THEN LAST_VALUE( start_date ) IGNORE NULLS
                OVER( PARTITION BY resource_id ORDER BY ROWNUM )
         END AS start_date,
         end_date
  FROM   (
    SELECT resource_id,
           CASE start_date
           WHEN LAG( end_date )
                  OVER ( PARTITION BY resource_id ORDER BY end_date )
           THEN NULL
           ELSE start_date
           END AS start_date,
           CASE end_date
           WHEN LEAD( start_date )
                  OVER ( PARTITION BY resource_id ORDER BY end_date )
           THEN NULL
           ELSE end_date
           END AS end_date
    FROM   (
      -- your query
    )
  )
)
WHERE  start_date IS NOT NULL
AND    end_date IS NOT NULL

score 0 · Answer 2 · answered Jun 22 '16 at 10:31

Here's an alternative way:

with sample_data as (select 24565865 resource_id, to_date('04/01/2016', 'dd/mm/yyyy') start_date, to_date('29/01/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('29/01/2016', 'dd/mm/yyyy') start_date, to_date('01/02/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('01/02/2016', 'dd/mm/yyyy') start_date, to_date('25/03/2016', 'dd/mm/yyyy') end_date from dual union all
--                     select 24565865 resource_id, to_date('25/03/2016', 'dd/mm/yyyy') start_date, to_date('01/04/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('01/04/2016', 'dd/mm/yyyy') start_date, to_date('09/05/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('09/05/2016', 'dd/mm/yyyy') start_date, to_date('13/05/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('13/05/2016', 'dd/mm/yyyy') start_date, to_date('25/07/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('25/07/2016', 'dd/mm/yyyy') start_date, to_date('01/08/2016', 'dd/mm/yyyy') end_date from dual union all
                     select 24565865 resource_id, to_date('01/08/2016', 'dd/mm/yyyy') start_date, to_date('12/12/2099', 'dd/mm/yyyy') end_date from dual)
select   resource_id,
         min(start_date) start_date,
         max(end_date) end_date
from     (select resource_id,
                 start_date,
                 end_date,
                 sum(sum_col) over (partition by resource_id order by start_date) grp
          from   (select resource_id,
                         start_date,
                         end_date,
                         case when lag(end_date, 1, start_date-1) over (partition by resource_id order by start_date) != start_date then 1 else 0 end sum_col
                  from   sample_data))
group by resource_id,
         grp;

RESOURCE_ID START_DATE END_DATE  
----------- ---------- ----------
   24565865 04-01-2016 25-03-2016
   24565865 01-04-2016 12-12-2099

Merge Dates into continous date

2 Answers2