CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Christian
Christian

Reputation: 83

Joining tables on ID and closest date

I have two tables, one showing when someone left and one showing when they came back (sometimes when they come back, they may forget to enter that they came back. I am tryint to join the tables so that they look like the desired table from the image.

enter image description here

Upvotes: 0

Views: 43

Answers (3)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

DECLARE @TableA TABLE(ID INT, Leave DATE)
INSERT INTO @TableA VALUES
(62175, '11/29/2019'),
(62175, '11/11/2019'),
(62175, '3/29/2019'),
(62175, '8/22/2019'),
(68454, '11/29/2019'),
(68454, '12/13/2019')


DECLARE @TableB TABLE(ID INT, [Return] DATE)
INSERT INTO @TableB VALUES
(62175, '4/4/2019'),
(62175, '11/16/2019'),
(62175, '11/30/2019'),
(68454, '11/30/2019'),
(68454, '12/14/2019')

SELECT TA.*, CASE WHEN ROW_NUMBER()OVER(PARTITION BY X.ID, X.[Return] ORDER BY TA.Leave DESC) = 1 THEN X.[Return] ELSE NULL END [Return] 
FROM @TableA TA
    OUTER APPLY (SELECT TOP 1 * FROM @TableB TB 
                    WHERE TA.ID = TB.ID 
                        AND TB.[Return] > TA.Leave 
                    ORDER BY TB.[Return] ) X
ORDER BY TA.ID, TA.Leave

Result:

ID          Leave      Return
----------- ---------- ----------
62175       2019-03-29 2019-04-04
62175       2019-08-22 NULL
62175       2019-11-11 2019-11-16
62175       2019-11-29 2019-11-30
68454       2019-11-29 2019-11-30
68454       2019-12-13 2019-12-14

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Very tricky question. I think this does what you want:

with ab as (
      select id, leave, null as return
      from a
      union all
      select id, null, return
      from b
     )
select distinct id, coalesce(leave, prev_leave), coalesce(return, next_return)
from (select ab.*,
             (case when leave is null
                   then lag(leave) over (partition by id order by coalesce(leave, return))
              end) as prev_leave, 
             (case when leave is null
                   then lead(leave) over (partition by id order by coalesce(leave, return))
              end) as next_return
      from ab
     ) ab

Upvotes: 0

Sean
Sean

Reputation: 1456

These tables are invalid, they should be in one table with 3 columns. ID, Leave, Return

Upvotes: 0

Related Questions