0

I would like to join on date that is the closest to my end date, whether it's before or after my end date.

TableA:

A.ID    A.StartDate    A.EndDate    A.ModifiedDate   
 1       1/1/17          1/15/18       1/16/18          
 2       2/1/17          3/1/18        3/2/18

TableB

B.SetDate  B.ID   Reason
 1/16/18   1       LeftGroup
 3/8/18    2       Booted
 3/6/18    2       Terminated

Output:should look like this

Final:

ID  StartDate   EndDate   ModifiedDate  SetDate   Reason
1    1/1/17       1/15/18   1/16/18      1/16/18   LeftGroup
2    2/1/17       3/1/18    3/4/18       3/6/18    Terminated

My query below:

select *
from TableA
 left join TableB b on a.id = b.id and AND TRUNC(SH.SET_DATE) BETWEEN TRUNC(CCP.STARTDATE) AND GREATEST(TRUNC(CCP.ENDDATE), TRUNC(CCP.MODIFIED_DATE))

--Need to add another join to pickup any loose enddates that do not have a reason associated to them.

something like left join TableB b2 on a.id = b2.id and ... and tableb is null

John
  • 289
  • 3
  • 14

1 Answers1

0

This is essentially answered in this post.

Where is says:

SELECT TOP 1 *
FROM x
WHERE x.date < 'somedate'
ORDER BY x.date DESC

HTH, Sean

Sean Devoy
  • 66
  • 5