Get latest date records from multiple tables in SQL Server

Hi smile,

Refer below query.

SQL

DECLARE @tblPregTest AS TABLE(TestID INT,TagID INT,I_Date DATE,C_Date DATE,Cost INT,P_Result VARCHAR(20))
INSERT INTO @tblPregTest VALUES(1,101,'2020-11-11','2020-08-30',0,'Positive')
INSERT INTO @tblPregTest VALUES(2,102,'2020-11-11','2020-08-30',0,'Positive')
INSERT INTO @tblPregTest VALUES(3,103,'2020-07-20','2020-08-30',0,'Positive')
INSERT INTO @tblPregTest VALUES(4,104,'2020-07-20','2020-08-30',0,'Positive')
INSERT INTO @tblPregTest VALUES(5,105,'2020-07-20','2020-08-30',0,'Negative')
INSERT INTO @tblPregTest VALUES(6,101,'2021-05-13','2021-07-13',0,'Positive')


DECLARE @tblCalving AS TABLE(CalvID INT,TagID INT,C_Date DATE)
INSERT INTO @tblCalving VALUES(1,104,'2021-08-04')
INSERT INTO @tblCalving VALUES(2,103,'2021-08-05')

SELECT t.TestID,t.TagID,t.C_Date
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY (TagID) ORDER BY C_Date DESC) Row_No,TestID,TagID,C_Date
    FROM @tblPregTest
    GROUP BY TestID,TagID,C_Date
)t
WHERE Row_No = 1 AND t.TagID NOT IN (SELECT DISTINCT TagID FROM @tblCalving)
ORDER BY t.TestID

Output

TestID     TagID        C_Date

2              102         2020-08-30

5              105         2020-08-30

6               101        2021-07-13