Compare tables and fetch latest date in SQL Server

smile
 
on Jul 13, 2021 11:58 PM
709 Views

Here, we need to compare Check_Date in tblPregnant with Calving_Date in tblCalving or with Abr_Date in tblAbortion. After comparing these three dates we will get/fetch data against latest date.

Here, first we will see TagID 101 in the tblPregnant and will check latest event for TagID 101 in tblPregTest and will examine tblCalving and check the Calving_Date for latest event. Here we did not found any date for TagID 101.

Now we will move ahead and examine next Table tblAbortion and will check the Abr_Date for latest event TagID 101. Here, in this table we found TagID 101 with latest date. Now we will compare this Abr_Date with Check_Date in tblPregnant. Here, in the tblAbortion Abr_Date is latest date for TagID 101. Hence data will be displayed like that. The same process will be repeat for each tag exist in the tblPregnant.

tblPregnant

PID

TagID

I_Date

Check-Date

1

101

2020-07-20

2020-08-30

2

102

2020-07-20

2020-08-30

3

103

2020-07-20

2020-08-30

4

104

2020-07-20

2020-08-30

5

105

2020-07-20

2020-08-30

tblCalving

CID

TagID

Calving_Date

1

103

2021-02-13

tblAbortion

ABID

TagID

Abr_Date

1

101

2021-01-13

2

102

2021-01-14

Hence, required oupt

Sr

TagID

Check_Date

Latest_Date [From tblCalving or tblAbortion]

1

101

2020-08-30

2021-01-13 [This date is found in tblAbortion]

2

102

2020-08-30

2021-01-14 [This date is found in tblAbortion]

3

103

2020-08-30

2021-02-13 [This date is found in tblCalving]

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 14, 2021 06:36 AM
on Jul 27, 2021 07:13 AM

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 

TagID Check_Date Latest_Date TableName
101 07/13/2021 07/13/2021 tblPregnant
102 08/30/2020 01/14/2021 tblAbortion
103 08/30/2020 02/13/2021 tblCalving
104 08/30/2020 02/13/2021 tblCalving