Hi Guys,
I have got a table of patients where each patient has got a unique patient number.There are different services which a patient can undertake such as resthome, dementia etc.
Here is the sample data
Service patientNo startDate EndDate
resthome 12 01/04/2003 03/05/2003
resthome 13 12/9/2004 13/10/2006
Dementia 44 12/08/2002 13/01/2004
dementia 12 05/05/2003 06/12/2006
................................. ............ ................................
Each patient has got a start date when he started undertaking that service and end date when the service finished .
Now I need to count total patients for each catagory but here is the main issue....
A patient can move from one servcice to another but cant be in two services at the same time and I need to count the most recent service which the patient is currently enrolled in..
How do I do that?
Pls help me guys as you have always done:wow:
Thanks
This will get you a list of the most recent service by startdate that the patient was enrolled in
Code Snippet
select ps.*
from patientservice ps
inner join
(select patientno, max(startdate) as startdate
from patientservices
group by patientno) z
on z.patientno = ps.patientno
and z.startdate = ps.startdate
|||Hi Cam
Thanks for your help
I ran this query but I dont know why is it giving me an error saying:
Invalid column name: startdate
Thanks
