Rewrite of query with correlated subquery in join condition-CodePudding

I have the query below which I am trying to rewrite. I have tried window functions and OUTER APPLY but it is difficult for me to get the logical meaning exactly the same. It seems like the below method is the only method to implement the logic.

SELECT  ...
FROM  dbo.Customer T  
  LEFT  JOIN dbo.CustSegment A 
            ON A.KEYCOL=T.KEYCOL  
            AND  A.date1=(select max(A1.date1) FROM  dbo.CustSegment A1 WHERE A1.KEYCOL=T.KEYCOL AND A1.date1<=T.date1) 
            AND A.date2=(select max(A2.date2) FROM  dbo.CustSegment A2 WHERE A2.KEYCOL=T.KEYCOL AND A2.date1=A.date1);

Extra information: date1 is some "effective date" and date2 is "registered date".

CodePudding user response：

I think this code should be equavalent:

SELECT  ...
FROM  dbo.Customer T  
OUTER APPLY (
       SELECT TOP 1 a.*
        FROM dbo.CustSegment A 
        WHERE A.KEYCOL=T.KEYCOL
        AND a.date1 <= t.date1  
        ORDER BY a.date1 DESC, a.date2 DESC
) zzz

Although, this kinda presumes you only want top row. If you want duplicates of dates then it won't work