March 24, 2010 at 2:13 am
Hi friends
I am working on a patient's database which is as follows(sample data)
Patient No. Drug_prescribed
21 Aspirin
21 morphine
21 panadol
22 aspirin
22 phosphate
23 panadol
24 aspirin
24 chlorate
.
.
.
I want to generate two reports-
1) Those patients on Aspirin which are also on panadol...
2) Those patients on Aspirin who are not on panadol but on chlorate and phosphate....
How do i do this??
March 24, 2010 at 3:45 am
would it be easier to have another table?
1st table stores the patients
2nd table stores the definitions of the drugs
3rd table stores the informatoin that links the two
-------------
Patients
-------------
ID Name
1 John Smith
2 Robert Smith
-------------
Drugs
-------------
ID Description
1 Morphine
2 Panadol
-------------
PatientDrugs
-------------
ID PatientID DrugID
1------2------1
March 24, 2010 at 5:32 am
Sticking with the design presented, here is one possible method:
CREATE TABLE #Sample
(
patient_no INTEGER NOT NULL,
drug_prescribed VARCHAR(50) NOT NULL,
PRIMARY KEY (patient_no, drug_prescribed)
);
INSERT #Sample
VALUES (21, 'aspirin'),
(21, 'morphine'),
(21, 'panadol'),
(22, 'aspirin'),
(22, 'phosphate'),
(23, 'panadol'),
(24, 'aspirin'),
(24, 'chlorate');
-- Patients on aspirin and panadol
SELECT P.patient_no
FROM #Sample S
PIVOT (
MAX(S.drug_prescribed) FOR
drug_prescribed IN (aspirin, panadol, chlorate, phosphate, morphine)
) P
WHERE aspirin IS NOT NULL
AND panadol IS NOT NULL;
-- Patients on aspirin, chlorate, and phophate
-- but not on panadol (no matches in test data)
SELECT P.patient_no
FROM #Sample S
PIVOT (
MAX(S.drug_prescribed) FOR
drug_prescribed IN (aspirin, panadol, chlorate, phosphate, morphine)
) P
WHERE aspirin IS NOT NULL
AND panadol IS NULL
AND chlorate IS NOT NULL
AND phosphate IS NOT NULL;
DROP TABLE #Sample
March 24, 2010 at 10:06 am
This one is more basic. It only shows the list of patients. However it produces a nice execution plan if we can assume that there is a primary key on the patient/medication columns.
declare @PatientMeds table (patient int, medication varchar(20), primary key (patient,medication))
insert into @PatientMeds
select 21,'Aspirin' union all
select 21,'Morphine' union all
select 21,'Panadol' union all
select 22,'Aspirin' union all
select 22,'Phosphate' union all
select 23,'Panadol' union all
select 24,'Aspirin' union all
select 24,'Chlorate' union all
select 24,'Phosphate'
-- patients on aspirin and panadol
select patient
from @patientMeds
where medication IN ('Aspirin','Panadol')
group by patient
having count(distinct medication) = 2
-- Patients NOT on panadol, but on aspirin, chlorate, and phophate
select patient
from @patientMeds pm1
where medication in ('Aspirin','Chlorate', 'Phosphate')
and not exists (Select 1 from @patientMeds pm2 where pm2.patient = pm1.patient and pm2.medication = 'Panadol')
group by patient
having count(distinct medication) = 3
I just saw, and like, your idea of pivoting the columns Paul. I'm going to throw out a quick crosstab solution as a tweak.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 24, 2010 at 12:35 pm
Here it is. Like the pivot, it avoids the index seek required by the NOT EXISTS. The execution plans appear to be close, if not identical.
declare @PatientMeds table (patient int, medication varchar(20), primary key (patient,medication))
insert into @PatientMeds
select 21,'Aspirin' union all
select 21,'Morphine' union all
select 21,'Panadol' union all
select 22,'Aspirin' union all
select 22,'Phosphate' union all
select 23,'Panadol' union all
select 24,'Aspirin' union all
select 24,'Chlorate' union all
select 24,'Phosphate'
-- patients on aspirin and panadol
;with cte as
(selectPatient,
max(case when medication = 'Aspirin' then 1 else 0 end) as Aspirin,
max(case when medication = 'Chlorate' then 1 else 0 end) as Chlorate,
max(case when medication = 'Morphine' then 1 else 0 end) as Morphine,
max(case when medication = 'Panadol' then 1 else 0 end) as Panadol,
max(case when medication = 'Phosphate' then 1 else 0 end) as Phosphate
from @patientMeds
where medication IN ('Aspirin','Panadol')
group by patient
)
select * from cte
where aspirin = 1 and panadol = 1
-- Patients NOT on panadol, but on aspirin, chlorate, and phophate
;with cte as
(selectPatient,
max(case when medication = 'Aspirin' then 1 else 0 end) as Aspirin,
max(case when medication = 'Chlorate' then 1 else 0 end) as Chlorate,
max(case when medication = 'Morphine' then 1 else 0 end) as Morphine,
max(case when medication = 'Panadol' then 1 else 0 end) as Panadol,
max(case when medication = 'Phosphate' then 1 else 0 end) as Phosphate
from @patientMeds
where medication IN ('Aspirin','Chlorate','Panadol','Phosphate')
group by patient
)
select * from cte
where aspirin = 1 and panadol = 0 and chlorate = 1 and phosphate = 1
[/code]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply