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?

Solution

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