swenri
Yak Posting Veteran
72 Posts |
Posted - 2012-11-28 : 11:07:31
|
I need a query that will check the expression and if true puts the expression in the alias.If the expression is not true put text ‘N/A’ in the alias.Last because of null and blank values I need the alias to have a text value of ‘N/N’ if the two above conditions are not met. These are in CASE statements of the query. Can anyone help me with this please. Thank you ********************************************************************************************SELECT mo_times.sessionid, MO_Times.RegistrationAdmissionTime, MO_Demographics.LastName, MO_Demographics.FirstName, MO_Demographics.MRN, MO_Demographics.AccountNumber, MO_Demographics.DateOfBirth, BVReports.ReportName, (BLUser_Names.firstname) as PATNFIRSTNAME, BLUser_Names.surname as PATNLASTNAME, BLUser_Names.title as PATNTITLE, BVProblems.Problem,(Select top 1case when ISNULL (Neonate.Vacuum_Extraction ,'0' ) = 'true' then 'VC' when ISNULL (Neonate.Forceps_Delivery,'0' )='true' then 'FD' when ISNULL (Neonate.Assisted_Breech_Delivery,'0' )='true' then 'ABD' when ISNULL (Neonate.Spontaneous_Breech_Delivery,'0')='true' then 'SBD' when ISNULL (Neonate.Total_Breech_Extraction,'0')='true' then 'TBE' when ISNULL (Neonate.Cesarean_Section,'0' )='true' then 'CS' when ISNULL (Neonate.Normal_Vaginal_Delivery,'0' )='true' then 'NVD' else 'N/A' endfrom DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate where BLSession_Extended.sessionID = Neonate.sessionid --AND Neonate.Vacuum_Extraction IS NOT NULL--OR Neonate.Forceps_Delivery IS NOT NULL--OR Neonate.Normal_Vaginal_Delivery IS NOT NULL) as ALLVD, --(Select top 1 f.ValueStr--from BVFindings f--where f.sessionid=BLSession_Extended.sessionID --and f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') as ANTYPE, Case when BVReports.ReportName = 'Dilatation & Curettage Report' then 'D&C' when BVReports.ReportName = 'Cerclage Report' then 'C&C' when BVReports.ReportName = 'Intrauterine Transfusion Report' then 'IUT' when BVReports.ReportName = 'Examination Under Anesthesia' then 'EUA' when BVReports.ReportName = 'General Operative Procedure' then 'GOP' when BVReports.ReportName = 'Bilateral Tubal Ligation Report' then 'BTL' else NULL end as ALLPRO,(Select top 1case when Complications.Difficult_Intubation='true' then 'DI' when Complications.Aspiration='true' then 'ASP' when Complications.Malignant_Hyperthermia='true' then 'MH' when Complications.Seizures= 'true' then 'S' when Complications.Headache_F='true' then 'H' when Complications.Paresthesia='true' then 'P' when Complications.Neurologic_Deficit='true' then 'ND' when Complications.Hypotension='true' then 'H' when Complications.Sinus_Bradycardia='true' then 'SB' when Complications.Arrhythmia='true' then 'ARR' when Complications.Respiratory_Depression='true' then 'RD' when Complications.Urinary_Retention='true' then 'UR' when Complications.Anaphylactic_Shock='true' then 'ASA' when Complications.Bronchospasm='true' then 'BRO' when Complications.Epidural_Abscess='true' then 'EA' when Complications.Fetal_Neonatal_Depression='true' then 'FND' when Complications.Other_Anesthesia_Complication='true' then 'OAC' else 'N/A' endfrom DatamartdB2.dbo.IPR_Anesthetic_Complications_Timed_Ane_Com_Tim as Complicationswhere BLSession_Extended.sessionID = Complications.sessionid) as ANCOMP, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') as ANTYPE, --NVD procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Midwife_Present!Normal_Vaginal_Delivery') as NVDsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Normal_Vaginal_Delivery') as NVDAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Normal_Vaginal_Delivery') as NVDRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as NVDTOD, --CS Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Cesarean_Section') as CSsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Cesarean_Section') as CSAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cesarean_Section') as CSRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as CSTOD,--D&C Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Dilatation_And_Curettage') as DCsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Dilatation_And_Curettage') as DCAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Dilatation_And_Curettage') as DCRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_to_OR!Dilatation_And_Curettage') as DCTIME,--Cerclage Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Cervical_Cerclage_A') as CCsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Cervical_Cerclage_A') as CCAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cervical_Cerclage_A') as CCRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_to_OR!Cervical_Cerclage_A') as CCTIME,--Bi tubal ligation Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Tubal_Ligation') as BTLsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Tubal_Ligation') as BTLAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Tubal_Ligation') as BTLRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!Tubal_Ligation') as BTLTIME,--General Operative Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!General_Operative_Procedure[#]') as GOPsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!General_Operative_Procedure[#]') as GOPAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!General_Operative_Procedure[#]') as GOPRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!General_Operative_Procedure[#]') as GOPTIME, --IUT Procedure (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!IUT') as IUTsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!IUT') as IUTAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!IUT') as IUTRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!IUT') as IUTTIME, --Exam under anesthesia Procedure (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Birth_Canal_Revision') as EUAsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Birth_Canal_Revision') as EUAAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Birth_Canal_Revision') as EUARES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!Birth_Canal_Revision') as EUATIME FROM DatamartDB2.dbo.BLSession_Extended BLSession_Extended JOIN DatamartDB2.dbo.MO_Times MO_Times ON BLSession_Extended.sessionID = MO_Times.SessionID JOIN DatamartDB2.dbo.MO_Demographics MO_Demographics ON BLSession_Extended.sessionID = MO_Demographics.SessionID --LEFT OUTER JOIN DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate IPR_Delivery_Report_Neonate_Delivery_Report_Neonate ON --BLSession_Extended.sessionID = IPR_Delivery_Report_Neonate_Delivery_Report_Neonate.sessionid --LEFT OUTER JOIN DatamartdB2.dbo.IPR_Anesthetic_Complications_Timed_Ane_Com_Tim IPR_Anesthetic_Complications_Timed_Ane_Com_Tim ON--BLSession_Extended.sessionID = IPR_Anesthetic_Complications_Timed_Ane_Com_Tim.sessionidLEFT OUTER JOIN DatamartDB2.dbo.BVReports BVReports onBLSession_Extended.sessionID = BVReports.sessionid and BVReports.ReportName = 'Post Anesthesia Transfer Note'LEFT OUTER JOIN DatamartDB2.dbo.BLUser_Names BLUser_Names ON BVReports.SignerId=BLUser_Names.userID LEFT OUTER JOIN DatamartDB2.dbo.BVProblems BVProblems onBLSession_Extended.sessionID = BVProblems.sessionid and BVProblems.Existence = 'EXISTS' WHERE ((MO_Times.RegistrationAdmissionTime >={ts '2012-06-11 00:00:00'}) AND (MO_Times.RegistrationAdmissionTime <{ts '2012-06-12 00:00:00'})) and BLSession_Extended.FacilityID =0 |
|