CODEWITHSUNDEEP
sqlsql-servermultiple-tables
Gert
Gert

Reputation: 21

Identify latest records from multiple tables and join them all together

I have 5 related tables I am trying to produce a query teport from.

What would be the process by which this can be achieved?

The end result should be:

Last Calving_Date Latest EC_Date Latest BScore Latest Group Cow ID 19-Jul-21 12-Jul-21 5-Jul-21 28-Jun-21
21/07/2021 15/12/2021 3 RED 18415 21 25 24 22
02/06/2021 11/11/2021 3.5 RED 18413 30 25 24 22
10/05/2021 15/10/2021 2.5 YELLOW 18419 27 25 26 21

At least 1 of the Week1... Week4 columns should have a value from Prod List. The table is sorted based on Last Calving_Date descending order.

The tables are:

Add_Cow_Event

Calving_Date Cow ID Event Type UID
21/07/2021 18415 Calved 100000001
10/05/2021 18419 Calved 100000002
11/06/2020 18415 Calved 100000003

PREGNANCY REGISTER

EC_DATE Cow ID UID
15/12/2021 18415 EC100000001
15/10/2021 18419 EC100000002
11/06/2020 18415 EC100000003

Animal Register

Cow ID Status
18415 Active
18419 Active
18413 Active

Body Scoring Table

Date Cow ID BScore BS_UID
21/07/2021 18415 3 BS1023
10/05/2021 18419 2.5 BS1024
11/06/2020 18415 3.5 BS1025

Feed Groups

Date Cow ID Color GC_UID
21/07/2021 18415 RED GC103
10/05/2021 18419 YELLOW GC104
11/06/2020 18415 BLUE GC105

PRODUCTION TABLE

Date Cow ID Shift Production
19/07/2021 18415 MORN 5 P103
19/07/2021 18415 NOON 5 P104
19/07/2021 18415 NIGHT 5 P105
19/07/2021 18419 MORN 10 P106
19/07/2021 18419 NOON 11 P107
19/07/2021 18419 NIGHT 6 P108
12/07/2021 18415 MORN 8 P109
12/07/2021 18415 NOON 12 P110
12/07/2021 18415 NIGHT 3 P111

My first step was to try and produce a table of the Latest Calving Dates using the below query:

SELECT
         Add_Cow_Event.Calving_Date,
         Add_Cow_Event.Event Type,
         Animal Register.Animal ID,
FROM  Add_Cow_Event
LEFT JOIN Animal Register ON Add_Cow_Event.CowID  = Animal Register.Animal ID  
WHERE    Add_Cow_Event.Event Type  = 'Calved'
ORDER BY Add_Cow_Event.Calving_Date DESC

This produces

Calving_Date Cow ID Event Type
21/07/2021 18415 Calved
10/05/2021 18419 Calved
11/06/2020 18415 Calved

Next, I need to find the latest Calving Date for every Cow ID and remove the older records from the table. How could this be achieved?

Upvotes: 1

Views: 97

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12969

You can go for ROW_NUMBER() with partition by and pick only the latest record for the cow as given below:

SELECT Calving_Date, Animal_ID as Cow_ID, Event_Type
FROM
(SELECT
         Add_Cow_Event.Calving_Date,
         Add_Cow_Event.Event_Type,
         Animal Register.Animal_ID,
         ROW_NUMBER() OVER(PARTITION BY Animal_ID ORDER BY calving_Date Desc) AS Rnk
FROM  Add_Cow_Event
LEFT JOIN Animal_Register ON Add_Cow_Event.CowID  = Animal_Register.Animal_ID  
WHERE    Add_Cow_Event.Event Type  = 'Calved') AS t
WHERE rnk = 1

Upvotes: 1

Related Questions