Compare tables and fetch latest date in SQL Server

Hi smile,

Refer below query.

SQL

DECLARE @tblPregnant AS TABLE(PID INT,TagID INT,I_Date DATE,Check_Date DATE,TableName VARCHAR(50))
INSERT INTO @tblPregnant VALUES(1,101,'07/20/2020','08/30/2020','tblPregnant')
INSERT INTO @tblPregnant VALUES(2,102,'07/20/2020','08/30/2020','tblPregnant')
INSERT INTO @tblPregnant VALUES(3,103,'07/20/2020','08/30/2020','tblPregnant')
INSERT INTO @tblPregnant VALUES(4,104,'07/20/2020','08/30/2020','tblPregnant')
INSERT INTO @tblPregnant VALUES(5,105,'07/20/2020','08/30/2020','tblPregnant')
INSERT INTO @tblPregnant VALUES(6,101,'05/13/2021','07/13/2021','tblPregnant')
  
DECLARE @tblCalving AS TABLE(CID INT,TagID INT,Calving_Date DATE,TableName VARCHAR(50))
INSERT INTO @tblCalving VALUES(1,103,'02/13/2021','tblCalving')
INSERT INTO @tblCalving VALUES(2,104,'02/13/2021','tblCalving')
  
DECLARE @tblAbortion AS TABLE(ABID INT,TagID INT,Abr_Date DATE,TableName VARCHAR(50))
INSERT INTO @tblAbortion VALUES(1,101,'01/13/2021','tblAbortion')
INSERT INTO @tblAbortion VALUES(2,102,'01/14/2021','tblAbortion')
  
SELECT x.TagID,MAX(tp.Check_Date) 'Check_Date',x.Check_Date 'Latest_Date',x.TableName FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY TagID ORDER BY Check_Date DESC) Row_No,
TagID,Check_Date,TableName FROM
(
SELECT TagID,Check_Date,TableName FROM @tblPregnant
UNION ALL
SELECT TagID,Calving_Date,TableName FROM @tblCalving
UNION ALL
SELECT TagID,Abr_Date,TableName FROM @tblAbortion
)t)x
INNER JOIN @tblPregnant tp ON tp.TagID = x.TagID
WHERE x.Row_No = 1 AND x.TagID IN
(
    SELECT TagID FROM @tblCalving
    UNION
    SELECT TagID FROM @tblAbortion
)
GROUP BY x.TagID,x.Check_Date,x.TableName

Output