I am using MS SQL Server 2008.
I have two tables. One is Patients, and the other is Visits.
The Patients table hold a list of patients which are uniquely identified using the pat_id column. The remainer of the table hold name, address etc.
The Visits table hold data regarding patient visits. It also has the pat_id column as a foriegn key into the Patients table.
The other columns that are interest are:
VisitDate : The data of the visit
Eye: a char(1). Can be 'L' (left) or 'R' (right)
Origin: a char(1). Set 'T' for the patients original visit. 'F' in all other cases.
I need to write a query that will return a summary of the patient visits. It should contain the following colums:
... other patient information ..
Number of visits
Number of visits for left eye
Number of visits for right eye
Lastest visit for left eye
Lastest visit for right eye
Earliest visit for left eye
Earliest visit for right eye
Date of visit for left eye where Origin = 'T'
Date of visit for right eye where Origin = 'T'
Note that a patient may only have visits for one eye.. though i guess this does not effect the query.
Can anyone put me together a query that will do the above?