Quick suggestion

😎

DECLARE @a TABLE (ID int, StartDate datetime)

DECLARE @b-2 TABLE (ID int, StartDate datetime)

INSERT INTO @b-2

SELECT 1,GETDATE()-5 UNION ALL

SELECT 1,GETDATE()-7 UNION ALL

SELECT 2,GETDATE()-8 UNION ALL

SELECT 3,GETDATE()-10 UNION ALL

SELECT 4,GETDATE()- 12 UNION ALL

SELECT 4,GETDATE()-13 UNION ALL

SELECT 5,GETDATE()-6 UNION ALL

SELECT 6,GETDATE()-7

INSERT INTO @a

SELECT 1,GETDATE()-4 UNION ALL

SELECT 1,GETDATE()-6 UNION ALL

SELECT 2,GETDATE()-7 UNION ALL

SELECT 3,GETDATE()-9 UNION ALL

SELECT 4,GETDATE()-12 UNION ALL

SELECT 4,GETDATE()-12 UNION ALL

SELECT 5,GETDATE()-6 UNION ALL

SELECT 6,GETDATE()-7

SELECT

A.ID

,A.StartDate

,B.StartDate

FROM @a A

INNER JOIN @b-2 B

ON A.ID = B.ID

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER

(

ORDER BY A.ID

,A.StartDate

) AS A_RID

,A.ID

,A.StartDate

FROM @a A

)

SELECT

A.ID

,A.StartDate

,MIN(B.StartDate) AS NextMininmumDateFromTable@B

FROM BASE_DATA A

INNER JOIN @b-2 B

ON A.ID = B.ID

WHERE B.StartDate <= A.StartDate

GROUP BY A.ID

,A.A_RID

,A.StartDate;

Output

ID StartDate NextMininmumDateFromTable@B

----------- ----------------------- ---------------------------

1 2016-02-02 10:16:36.703 2016-02-01 10:16:36.703

1 2016-02-04 10:16:36.703 2016-02-01 10:16:36.703

2 2016-02-01 10:16:36.703 2016-01-31 10:16:36.703

3 2016-01-30 10:16:36.703 2016-01-29 10:16:36.703

4 2016-01-27 10:16:36.703 2016-01-26 10:16:36.703

4 2016-01-27 10:16:36.703 2016-01-26 10:16:36.703

5 2016-02-02 10:16:36.703 2016-02-02 10:16:36.703

6 2016-02-01 10:16:36.703 2016-02-01 10:16:36.703