I would like a little help in forming a strategy and query to solve a problem i have.
I have a table called Patients that contains a list of patients. (One row per patient)
There are three other tables called Population, Incidents and Surgery. These three tables have a foreign key realtionship with the Patients table.
A patient does not have to have a row in any of the three tables, but can also have multiple rows. Each row in these tables has a Registration Date which combined with the patient foreign key makes the row unique.
I need to create view over the three tables that shows the Patient and his/her LAST entry in each table.
What would be the best strategy for creating this view? Should i create CTEs for each table that return the latest row for each patient. An then join the CTEs with the Patients table? Is there a better way?