SQl query help

Question

Post reply

SQl query help

Lucky9

Default port

Points: 1470
More actions
December 26, 2010 at 10:29 pm

#242798

Create table patient
(PatientID int,
TreatmentID int,
TreatmentStartDate datetime,
TreatmentEndDate datetime)
Thanks a lot for the help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

How many entries per patient ID can hav ? only 2 or more ?

and, do we have a patient IDs stored in the DB sequentially and whats the Primary key of your table ?

Does your table have a ID column to identify each row ?

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

Hi..Try this,

SELECT b.PatientID , b.TreatmentID , 'Overlaps ' + a.PatientID + ' ' + a.TreatmentID AS 'Overlaps'

FROM Patient a, Patient b

WHERE a.PatientID != b.PatientID and

b.TreatmentStartDate >= a.TreatmentStartDate and

b.TreatmentStartDate <= a.TreatmentEndDate

ORDER BY b.PatientID;

Lucky9 Default port Points: 1470 More actions · Answer 3

There might be more then 2 or more treatments for the patient... and Treatment ID is a primary key..

Bharat Panthee Default port Points: 1448 More actions · Answer 4

I think this query will help you

select PatientID,first_treatment_id,TreatmentStartDate,TreatmentEndDate,STUFF((SELECT ',' AS "text()",

TreatmentID AS "text()"

FROM patient P

where P.PatientID =B.PatientID and P.TreatmentID <> B.first_treatment_id

FOR XML PATH('')),1,1,'') as Other_treatment_Ids from (select PatientID,Patient.TreatmentID as first_treatment_id,TreatmentStartDate,TreatmentEndDate, dense_RANK() over(partition by PatientID order by TreatmentStartDate) treatment_Nr

from patient

)B

where B.treatment_Nr <2

The column Other_treatment_Ids will give the other Treatment_Ids related to that particular patient.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

My query will work considering the fact that the treatment Id will always be incrementing for each patientid whose treatments are overlapping.

select * from patient p1

where exists(select 1 from patient p2 where p2.PatientID=p1.PatientID

and p1.TreatmentStartDate<=p2.TreatmentEndDate and p1.TreatmentID>p2.TreatmentID)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server