Patient query... Pls help

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