your advice on how to create this one 1

[din2005]

Hi all,

what i am trying to do is show patients who have been treated. but the criteria i would want is that each patient can have many treatments for example

joe blogs was treated in 01/01/2000 and 08/10/2000. However some patients who have had multiple treatments there treatment date may be 01/01/2000 and 01/01/2003.

my question is how do i show patients who have been treated not greater than a 2 year gap.

is there a criteria i could put in the treatdate field?

cheers

 

[KenReay]

Hi

could you define the statement "my question is how do i show patients who have been treated not greater than a 2 year gap." more precisely

for example do you mean last treatment is within two years, or that the gap between treatments is less than two years (in which case what about patients with only one treatment record?)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[din2005]

My appologies, what i mean is "gap between treatments is less than two years" i would like to ignore patients who have had one treatment!

many thanks

 

[Crowley16]

you can subtrace the 2 dates, (datediff) and test if this is larger than 2 years...

--------------------
Procrastinate Now!

 

[PHV]

You may try something like this:
SELECT A.PatientID, A.TreatmentDate, Max(B.TreatmentDate) AS LastTreatment
FROM yourTable AS A INNER JOIN yourTable AS B
ON A.PatientID = B.PatientID AND A.TreatmentDate > B.TreatmentDate
GROUP BY A.PatientID, A.TreatmentDate
HAVING Max(B.TreatmentDate) >= A.TreatmentDate - 730;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

thanks for the replies.

To phv how would it look with this sql if i used your solution.

SELECT PATIENTS.Hospital_no, PATIENTS.firstname, PATIENTS.Surname, TREATMENT.TREATDATE, TREATMENT.LESION, TREATMENT.PATHOLOGY
FROM PATIENTS INNER JOIN TREATMENT ON PATIENTS.Hospital_no = TREATMENT.HOSP_NO
WHERE (((TREATMENT.PATHOLOGY)="AVM"));

 

[PHV]

Should PATHOLOGY be 'AVM' for the 2 treatments inside the 2 years gap ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

yep it filters the patient down further. Patient can have various pathologies i.e tumours or lesions

cheers

 

[PHV]

Parhaps something like this:
SELECT P.Hospital_no, P.firstname, P.Surname, A.TREATDATE, A.LESION, A.PATHOLOGY, Max(B.TREATDATE) AS LastSamePathology
FROM PATIENTS P INNER JOIN (
TREATMENT A INNER JOIN TREATMENT B ON A.HOSP_NO=B.HOSP_NO AND A.PATHOLOGY=B.PATHOLOGY AND A.TREATDATE>B.TREATDATE
) ON P.Hospital_no=A.HOSP_NO
WHERE A.PATHOLOGY='AVM'
GROUP BY P.Hospital_no, P.firstname, P.Surname, A.TREATDATE, A.LESION, A.PATHOLOGY
HAVING Max(B.TREATDATE) >= A.TREATDATE-730;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886