Hi smile,
Use below query. Use MAX function.
SQL
DECLARE @tblBreading AS TABLE(BID INT,TagID INT,I_Date DATE)
INSERT INTO @tblBreading VALUES(19,101,'2019-09-17')
INSERT INTO @tblBreading VALUES(20,102,'2019-09-16')
INSERT INTO @tblBreading VALUES(21,103,'2019-09-15')
INSERT INTO @tblBreading VALUES(22,102,'2019-10-16')
INSERT INTO @tblBreading VALUES(23,103,'2019-11-17')
DECLARE @tblPregTest AS TABLE(TestID INT,TagID INT,C_Date DATE)
INSERT INTO @tblPregTest VALUES(9 ,101,'2020-01-12')
INSERT INTO @tblPregTest VALUES(10,102,'2020-01-12')
INSERT INTO @tblPregTest VALUES(11,103,'2020-01-12')
INSERT INTO @tblPregTest VALUES(13,103,'2020-01-12')
DECLARE @tblCalving AS TABLE(CalvID INT,TagID INT,C_Date DATE)
INSERT INTO @tblCalving VALUES(7,103,'2020-08-18')
INSERT INTO @tblCalving VALUES(8,102,'2020-06-10')
DECLARE @tblAbortion AS TABLE(ABID INT,TagID INT,Abr_Date DATE)
INSERT INTO @tblAbortion VALUES(7,103,'2019-08-18')
INSERT INTO @tblAbortion VALUES(8,102,'2019-06-10')
;WITH CTE
AS (
select BID as 'ID',TagID,I_Date as 'Date','tblBreading' as TableName from @tblBreading where BID in (SELECT MAX(BID) FROM @tblBreading group by TagID)
--union
--select BID,TagID,I_Date,'tblBreading' from @tblBreading where BullID is not null and BID in (SELECT MAX(BID) FROM @tblBreading group by TagID)
union
select TestID,TagID,C_Date,'tblPregTest' from @tblPregTest where TestID in (SELECT MAX(TestID) FROM @tblPregTest group by TagID)
union
select CalvID,TagID,C_Date,'tblCalving' from @tblCalving where CalvID in (SELECT MAX(CalvID) FROM @tblCalving group by TagID)
union
select ABID,TagID,Abr_Date,'tblAbortion' from @tblAbortion where ABID in (SELECT MAX(ABID) FROM @tblAbortion group by ABID)
)
SELECT t1.TagID,t1.[Date],(SELECT TableName FROM CTE t2 WHERE t1.Date = t2.Date AND t1.TagID = t2.TagID) TableName
FROM (
SELECT TagID,MAX(Date) [Date]
FROM CTE
GROUP BY TagID
) t1
ORDER BY TagID
Output
| TagID |
Date |
TableName |
| 101 |
2020-01-12 |
tblPregTest |
| 102 |
2020-06-10 |
tblCalving |
| 103 |
2020-08-18 |
tblCalving |
