Get latest date records from multiple tables in SQL Server

smile
 
on Aug 31, 2021 11:05 PM
530 Views

fetching data from two tables with latest occurrence in one Table

tblPregTest

TestID TagID I_Date C_Date Cost P_Result

1 101 2020-11-11 2020-08-30 0 Positive

2 102 2020-11-11 2020-08-30 0 Positive

3 103 2020-07-20 2020-08-30 0 Positive

4 104 2020-07-20 2020-08-30 0 Positive

5 105 2020-07-20 2020-08-30 0 Negative

6 101 2021-05-13 2021-07-13 0 Positive

tblCalving

CalvID TagID C_Date

1 104 2021-08-04

2 103 2021-08-05

required Output with following Condition and requirements:

select (by latest event) TestID,TagID,C_Date 
from tblPregTest where P_Result='Positive'
--and these TagID are not present in tblCalving

TestID TagID C_Date

2 102 2020-08-30

6 101 2021-07-13

if TagID 102 from above tblPregtest goes to tblCalving in any time then output should be like that

TestID TagID C_Date

6 101 2021-07-13

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 01, 2021 06:35 AM

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