Multiple Joins of One Table in SQL

Question

I'm sure this is answered somewhere, but I'm not quite sure how to search for it.

I have a table, [tbl_Data], that looks like this:

ID| From_Dt   | To_Dt     |
1 |01/01/2016 | 01/03/2016|
1 |01/04/2016 | 01/09/2016|
1 |01/09/2016 | 01/20/2016|

Want the output to look like:

ID|Prev_From_Dt|Prev_To_Dt |Recent_From_Dt|Recent_To_Dt|
1 |01/01/2016  |01/03/2016 |01/04/2016    |01/09/2016  |
1 |01/04/2016  |01/09/2016 |01/09/2016    |01/20/2016  |

This is my attempt at the code:

SELECT 
  t1.ID,
  Prev_From_DT = t1.FROM_DT,
  Prev_To_DT = t1.TO_DT,
  Recent_From_DT = t2.FROM_DT,
  Recent_To_DT = t2.TO_DT
FROM 
  tbl_Data t1
  LEFT JOIN tbl_Data t2 on t1.ID = t2.ID AND t1.TO_DT <= t2.FROM_DT

Which version of SQL are you using? (MySQL, Postgres, etc.) If it's MySQL, then you will probably find this SO answer helpful http://stackoverflow.com/questions/1284441/how-does-a-mysql-self-join-work (the magic phrase is "self join" - a table joined with itself). — Bob Salmon, May 26 '16 at 19:49
Using MSSQL. The query I provided creates a record for every From_Dt that is less than the To_Dt. — CurlieQ1034, May 26 '16 at 19:53

Vamsi Prabhala · Answer 1 · 2016-05-26T20:05:31.103

You can calculate rownumbers and then join on the previous row to get the current row's and the next row's date on the same row.

with rownums as (select *,row_number() over(partition by id order by from_dt) rn from t)
select 
r1.id,
r1.from_dt prev_from_dt,r1.to_dt prev_to_dt,
r2.from_dt recent_from_dt,r2.to_dt recent_to_dt
from rownums r1
join rownums r2 on r1.rn = r2.rn-1 and r1.id=r2.id

Or simply use the lead function to get the values on the next row.

select * from (
select id, 
from_dt prev_from_dt,to_dt prev_to_dt
lead(from_dt) over(partition by id order by from_dt) recent_from_dt,   
lead(to_dt) over(partition by id order by from_dt) recent_to_dt
from t) x
where recent_from_dt is not null and recent_to_dt is not null

Multiple Joins of One Table in SQL

1 Answers1