traversing the records in sql

Question

I would like to get the output for the over lapping date records

> Data: Id Open_date Closed_Date
>       1  2016-01-01 2017-01-01
**>       1  2016-12-31 2018-21-01
>       1  2016-01-01 2018-01-01**
>       2  2017-01-01 2018-02-02
Here, you see the second & 3rd records are starting with date than the closed_Date of their previous records. Here i need to identify those type of records

Possible duplicate of https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — yu yang Jian, May 25 '18 at 06:21
let's not consider Id as primary key column..slight correction — Hari, May 25 '18 at 06:25
Search for the infamous Gap and Islands problem (tons of solutions online), and tag which DBMS are you using. — EzLo, May 25 '18 at 06:32

score 0 · Answer 1 · answered May 25 '18 at 07:25

0

As you question is not much clear, I am assuming that you are looking for min of open date and max of close date.
If this is not the requirement edit the question to provide more details.

select id, min(Open_date), max(Closed_Date)
from table
group by id

answered May 25 '18 at 07:25

Gaurav

1,070
9
11

Edited the question! – Hari May 29 '18 at 13:40

score 0 · Answer 2 · answered May 25 '18 at 08:47

Looks like you want to normalize a Slowly Changing Dimension Type 2. Of course the best way to handle them would be using Temporal tables using either Teradata or ANSI syntax.

There's a nice syntax in Teradata to get your expected result based on the Period data type, but it's imple to cast your begin/end dates to a period:

SELECT id,
   -- split the period back into seperate dates
   Begin(pd) AS Open_date,
   End(pd) AS Closed_Date
FROM
 (
   SELECT NORMALIZE -- magic keyword :-)
      id, PERIOD(Open_date, Closed_Date) AS pd
   FROM tab
 ) AS dt

traversing the records in sql

2 Answers2