Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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?

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

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
```

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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.

```
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
*/
```

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 trial11: ,(20,'2018-07-01','2018-08

12: ,(20,'2018-08-01','2018-09

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?

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

PostgreSQL

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.