Need help with query.

Question

Need help with query.

NineIron

SSChampion

Points: 12536
More actions
May 1, 2014 at 6:16 am

#286336

The data contains medications given to patients. If a patient is given Vitamin K, DrugID='VITA100T2', then, the status can be 'AC' or 'DC'. All other drugs with a status of 'DC' should be excluded from the results.
This is my query to return the results for the patient but, does not return Vitamin K, if the status is 'DC'. I know there are several ways to handle this but, I need the best performing solution.
select a.VisitID, a.DrugID, a.Name, a.Dose
from dbo.zcus_ufn_PhaRxMedications('BRO','9749634931') a
inner join DPhaDrugData c on
a.SourceID = c.SourceID and a.DrugID=c.DrugID
where c.GenericID not in ('WARMAIN','WAR')
and a.Status='AC'
group by a.VisitID, a.DrugID, a.Name, a.Dose
create table dbo.DRUGS
(
VisitID char(10),
DrugID varchar(20),
Name varchar(50),
Dose varchar(5),
Status char(2)
)
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ALBINHFL', 'Ventolin HFA','1','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'AMLO5', 'Norvasc','10','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'APAP', 'Tylenol','650','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'APAP', 'Tylenol','650','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ATOR80', 'Lipitor','80','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'BEN25', 'Benadryl','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'BEN25', 'Benadryl','25','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'CEFAZ1', 'Kefzol','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'CEFAZ2NS', 'Kefzol','NULL','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'CIT10', 'CELExa','10','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'DEX4P', 'Decadron','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'DIL2VIAL', 'Dilaudid','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'DSS', 'Colace','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'EPHEDINJ', 'ePHEDrine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'FENT100VL', 'Sublimaze','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'GABA100', 'Neurontin','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'GABA100', 'Neurontin','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'HCTZ25', 'Oretic','25','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'INSHUMALOG', 'HumaLOG Insulin','0','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'INSLANTUS', 'Lantus Insulin','75','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'KETO30P', 'Toradol','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'LIS40', 'Zestril','40','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'LOP50', 'Lopressor','50','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'LR1000', 'LR','NULL','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MAG1GMD5W', 'Magnesium Sulfate','1','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MAGOXB', 'Mag-Ox','400','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MARC25EP', 'Marcaine 0.25% W/Epi-MPF','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MET500', 'Glucophage','1000','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MIRALAX', 'Miralax','17','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MOM30L', 'Milk of Magnesia','30','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'NAROPIN', 'Naropin 0.5%','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ONDAN2P', 'Zofran','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ONDAN2P', 'Zofran','4','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'OXYCODCR', 'OxyCONTIN','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PCET', 'Percocet','1','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PCET', 'Percocet','2','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PHENYLEPHI', 'Neo-Synephrine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PROP', 'diPRIVAN','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'QUEXR300', 'SEROquel XR','600','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ROB2P', 'Robinul','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SPSUDL', 'Kayexelate','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SPSUDL', 'Kayexelate','30','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SUCCINYL', 'Anectine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SULFCR50', 'Silvadene','0','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'X5', 'xaNAX','0.5','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'X5', 'xaNAX','0.5','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'APAP500', 'Tylenol Extra-Strength','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'BABYASA', 'ASA','81','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'CEFAZ1', 'Kefzol','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'DSS', 'Colace','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'LOP50', 'Lopressor','25','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'MEPHYTON', 'Mephyton','2.5','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'METHY125', 'Solu-MEDROL','60','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'MORPH2TUB', 'morphine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'P20', 'Deltasone','20','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'PAN40', 'Protonix','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'PAN40', 'Protonix','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'PNEUMOVAX', 'Pneumovax','0.5','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'SIM20', 'Zocor','10','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'SWI10', 'Sterile Water Pres-Free Inj','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'TORS20', 'Demadex','20','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'TRAM50', 'Ultram','50','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'ALBINHFL', 'Ventolin HFA','2','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'AMLO5', 'Norvasc','10','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'APAP', 'Tylenol','650','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'DSS', 'Colace','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'FURO20P', 'LASIx','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'FURO40P', 'LASIx','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'GABA300', 'Neurontin','300','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'HEP5V', 'hePARIN (Porcine)','5000','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'IPRASOL', 'Atrovent/NS Neb','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'L20', 'LASIx','40','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'METHY125', 'Solu-MEDROL','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'MOM30L', 'Milk of Magnesia','30','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'NYSTATPOW', 'Mycostatin','100000','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'PAN20', 'Protonix','20','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'ROP1', 'Requip','1','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'TOPXL50', 'ToPROL XL','50','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654120', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'VITA100T2', 'Vitamin K','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654118', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654118', 'Z100', 'Zyloprim','100','AC')

Viewing 3 posts - 1 through 2 (of 2 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

NineIron (5/1/2014)
The data contains medications given to patients. If a patient is given Vitamin K, DrugID='VITA100T2', then, the status can be 'AC' or 'DC'. All other drugs with a status of 'DC' should be excluded from the results.
This is my query to return the results for the patient but, does not return Vitamin K, if the status is 'DC'. I know there are several ways to handle this but, I need the best performing solution.
select a.VisitID, a.DrugID, a.Name, a.Dose
from dbo.zcus_ufn_PhaRxMedications('BRO','9749634931') a
inner join DPhaDrugData c on
a.SourceID = c.SourceID and a.DrugID=c.DrugID
where c.GenericID not in ('WARMAIN','WAR')
and a.Status='AC'
group by a.VisitID, a.DrugID, a.Name, a.Dose
create table dbo.DRUGS
(
VisitID char(10),
DrugID varchar(20),
Name varchar(50),
Dose varchar(5),
Status char(2)
)
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ALBINHFL', 'Ventolin HFA','1','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'AMLO5', 'Norvasc','10','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'APAP', 'Tylenol','650','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'APAP', 'Tylenol','650','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ATOR80', 'Lipitor','80','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'BEN25', 'Benadryl','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'BEN25', 'Benadryl','25','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'CEFAZ1', 'Kefzol','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'CEFAZ2NS', 'Kefzol','NULL','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'CIT10', 'CELExa','10','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'DEX4P', 'Decadron','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'DIL2VIAL', 'Dilaudid','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'DSS', 'Colace','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'EPHEDINJ', 'ePHEDrine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'FENT100VL', 'Sublimaze','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'GABA100', 'Neurontin','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'GABA100', 'Neurontin','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'HCTZ25', 'Oretic','25','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'INSHUMALOG', 'HumaLOG Insulin','0','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'INSLANTUS', 'Lantus Insulin','75','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'KETO30P', 'Toradol','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'LIS40', 'Zestril','40','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'LOP50', 'Lopressor','50','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'LR1000', 'LR','NULL','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MAG1GMD5W', 'Magnesium Sulfate','1','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MAGOXB', 'Mag-Ox','400','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MARC25EP', 'Marcaine 0.25% W/Epi-MPF','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MET500', 'Glucophage','1000','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MIRALAX', 'Miralax','17','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'MOM30L', 'Milk of Magnesia','30','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'NAROPIN', 'Naropin 0.5%','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ONDAN2P', 'Zofran','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ONDAN2P', 'Zofran','4','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'OXYCODCR', 'OxyCONTIN','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PCET', 'Percocet','1','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PCET', 'Percocet','2','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PHENYLEPHI', 'Neo-Synephrine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'PROP', 'diPRIVAN','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'QUEXR300', 'SEROquel XR','600','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'ROB2P', 'Robinul','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SPSUDL', 'Kayexelate','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SPSUDL', 'Kayexelate','30','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SUCCINYL', 'Anectine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'SULFCR50', 'Silvadene','0','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'X5', 'xaNAX','0.5','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749634931', 'X5', 'xaNAX','0.5','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'APAP500', 'Tylenol Extra-Strength','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'BABYASA', 'ASA','81','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'CEFAZ1', 'Kefzol','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'DSS', 'Colace','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'LOP50', 'Lopressor','25','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'MEPHYTON', 'Mephyton','2.5','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'METHY125', 'Solu-MEDROL','60','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'MORPH2TUB', 'morphine','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'P20', 'Deltasone','20','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'PAN40', 'Protonix','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'PAN40', 'Protonix','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'PNEUMOVAX', 'Pneumovax','0.5','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'SIM20', 'Zocor','10','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'SWI10', 'Sterile Water Pres-Free Inj','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'TORS20', 'Demadex','20','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'TRAM50', 'Ultram','50','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749651082', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'ALBINHFL', 'Ventolin HFA','2','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'AMLO5', 'Norvasc','10','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'APAP', 'Tylenol','650','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'DSS', 'Colace','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'FURO20P', 'LASIx','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'FURO40P', 'LASIx','40','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'GABA300', 'Neurontin','300','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'HEP5V', 'hePARIN (Porcine)','5000','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'IPRASOL', 'Atrovent/NS Neb','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'L20', 'LASIx','40','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'METHY125', 'Solu-MEDROL','0','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'MOM30L', 'Milk of Magnesia','30','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'NYSTATPOW', 'Mycostatin','100000','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'PAN20', 'Protonix','20','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'ROP1', 'Requip','1','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'TOPXL50', 'ToPROL XL','50','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654120', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654117', 'VITA100T2', 'Vitamin K','100','AC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654118', 'VITA100T2', 'Vitamin K','100','DC')
insert into dbo.DRUGS(VisitID, DrugID, Name, Dose, Status) values('9749654118', 'Z100', 'Zyloprim','100','AC')

why not just add an additional test to the WHERE clause:

select a.VisitID, a.DrugID, a.Name, a.Dose

from dbo.zcus_ufn_PhaRxMedications('BRO','9749634931') a

inner join DPhaDrugData c on

a.SourceID = c.SourceID and a.DrugID=c.DrugID

where c.GenericID not in ('WARMAIN','WAR')

and (a.Status='AC'

or (a.Status in ('AC, 'DC') and a.DrugID = 'VITA100T2'))

group by a.VisitID, a.DrugID, a.Name, a.Dose

NineIron SSChampion Points: 12536 More actions · Answer 2

NineIron

SSChampion

Points: 12536

May 1, 2014 at 6:33 am

#1710079

Thanx. Never gave that a thought.