Akash Panwar Akash Panwar - 5 months ago 12
SQL Question

Merge Dates into continous date

|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

MT0 MT0
Answer

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
Comments