Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with query. Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 6:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:55 AM
Points: 122, Visits: 188
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')



Post #1566683
Posted Thursday, May 1, 2014 6:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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

Post #1566688
Posted Thursday, May 1, 2014 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:55 AM
Points: 122, Visits: 188
Thanx. Never gave that a thought.
Post #1566690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse