Prior End Date - Start date match

Hi,

  We are on Postgresql 9.6.1.
I have a table which has an ID attribute, start_Dt and end_Dt.
Need to find the first_start_dt where there is a chain of the prior end_dt beging the same as the current start_Dt.

I have the example data and the desired output below.
Can someone kindly help?

WITH AA
AS
(SELECT ID,START_DT,END_DT
  FROM 
  (
  VALUES
     (10,'2018-01-01','2018-01-31')
    ,(10,'2018-02-04','2018-03-28')
    ,(10,'2018-03-28','2018-05-20'
    ,(10,'2018-05-20',NULL)
    ,(20,'2018-07-01','2018-08-10')
    ,(20,'2018-08-01','2018-09-10')
  ) tmp(ID,START_DT,END_DT)
)

SELECT ID,start_dt,end_Dt
 FROM AA

Open in new window


Desired result:
 
 id        start_dt               end_dt
 10     2018-01-01        2018-01-31
 10     2018-02-04           null
 20     2018-07-01         2018-09-10
pvsbandiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Please help me so I understand correctly - you need the records group by ID obviously where start_dt and end_dt are both on the same Monthly series - correct? In that case we can use MAX and DATEPART functions to match the records you want to show in the result set but...in that case why does the ID = 10 have 2 separate "series"?
pvsbandiAuthor Commented:
No, that's not what i want.

    For example:
   For ID 10:
     2nd Row, has start_Dt = 2018-02-04 and end_Dt = 2018-03-28
Now, 3rd row has start_dt = 2018-03-28, which is the same as the previous row's end date. It has an end date = 2018-05-20
Not stopping at that, we proceed to look at 4th row, where the start_Dt = 2018-05-20, which is the same as the previous end date.
This last row has an end_dt as NULL.
So, we have to stitch this episode to say:
Start_Dt = 2018-02-04 and the end_dt is NULL

Hope i'm clear.
SharathData EngineerCommented:
Can you try this?

WITH AA(ID, start_dt, end_dt) AS (
VALUES
     (10,'2018-01-01','2018-01-31')
    ,(10,'2018-02-04','2018-03-28')
    ,(10,'2018-03-28','2018-05-20')
    ,(10,'2018-05-20', NULL)
    ,(20,'2018-07-01','2018-08-10')
    ,(20,'2018-08-01','2018-09-10')),
CTE AS (SELECT ID, start_dt, COALESCE(end_dt, '9999-12-31') end_dt FROM AA),
CTE1 AS (
SELECT t1.ID, t1.start_dt, t1.end_dt, t2.start_dt t2_start_dt, t2.end_dt t2_end_dt, t3.start_dt t3_start_dt, t3.end_dt t3_end_dt
       ,ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.start_dt) rn
  FROM CTE t1
  LEFT JOIN CTE t2 ON t1.ID = t2.ID AND t2.start_dt > t1.start_dt AND t2.start_dt <= t1.end_dt
  LEFT JOIN CTE t3 ON t1.ID = t3.ID AND t3.start_dt < t1.start_dt AND t3.end_dt >= t1.start_dt),
CTE2 AS (
SELECT ID, start_dt, end_dt, rn FROM CTE1 
 UNION 
SELECT ID, t2_start_dt, t2_end_dt, rn FROM CTE1
 UNION
SELECT ID, t3_start_dt, t3_end_dt, rn FROM CTE1),
CTE3 AS ( SELECT ID, rn, MIN(start_dt) start_dt, MAX(end_dt) end_dt FROM CTE2 GROUP BY ID, rn),
CTE4 AS ( SELECT ID, start_dt, MAX(end_dt) end_dt FROM CTE3 GROUP BY ID, start_dt)
SELECT ID, MIN(start_dt) start_dt, CASE WHEN end_dt = '9999-12-31' THEN NULL ELSE end_dt END end_dt 
  FROM CTE4 
 GROUP BY ID, end_dt 
 ORDER BY ID, start_dt;
/*
id	start_dt	end_dt
10	2018-01-01	2018-01-31
10	2018-02-04	
20	2018-07-01	2018-09-10
*/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

awking00Information Technology SpecialistCommented:
On lines 11 and 12 of your example -
11:    ,(20,'2018-07-01','2018-08-10')
12:    ,(20,'2018-08-01','2018-09-10')
Was line 11's end_dt a typo and meant to be '2018-08-01' to match the start_dt on line 12 or does the "chain" continue because the range on line 11 overlaps the start_dt on line 12?
pvsbandiAuthor Commented:
Hi,
  Yes, it was a typo, but if the prior end date is not equal to the current start date, then the episode won't "stitch", so will stay as is.
But if the chain can be connected for n number of rows, not just limited to 3 rows as in my example.
Please let me know if that makes sense
SharathData EngineerCommented:
Did you check my post?
pvsbandiAuthor Commented:
Thanks, Sharat!  It works fine.
Sorry, i missed your post.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.