asked on

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

lcohan

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"?

pvsbandi

ASKER

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.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

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?

pvsbandi

ASKER

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

Sharath S

Did you check my post?

pvsbandi

ASKER

Thanks, Sharat! It works fine.
Sorry, i missed your post.