Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with NULLS in a CASE query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

72 Posts

Posted - 11/28/2012 :  11:07:31  Show Profile  Reply with Quote
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 1
case 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' end
from 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 1
case 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' end
from DatamartdB2.dbo.IPR_Anesthetic_Complications_Timed_Ane_Com_Tim as Complications
where BLSession_Extended.sessionID = Complications.sessionid) as ANCOMP,

(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID
and f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') as ANTYPE,
--NVD procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Midwife_Present!Normal_Vaginal_Delivery') as NVDsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Normal_Vaginal_Delivery') as NVDAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Normal_Vaginal_Delivery') as NVDRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as NVDTOD,
--CS Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Cesarean_Section') as CSsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Cesarean_Section') as CSAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cesarean_Section') as CSRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as CSTOD,
--D&C Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Dilatation_And_Curettage') as DCsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Dilatation_And_Curettage') as DCAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Dilatation_And_Curettage') as DCRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_to_OR!Dilatation_And_Curettage') as DCTIME,
--Cerclage Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Cervical_Cerclage_A') as CCsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Cervical_Cerclage_A') as CCAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cervical_Cerclage_A') as CCRES,
(Select top 1 f.ValueTime
from BVFindings f
where 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.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Tubal_Ligation') as BTLsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Tubal_Ligation') as BTLAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Tubal_Ligation') as BTLRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!Tubal_Ligation') as BTLTIME,
--General Operative Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!General_Operative_Procedure[#]') as GOPsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!General_Operative_Procedure[#]') as GOPAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!General_Operative_Procedure[#]') as GOPRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!General_Operative_Procedure[#]') as GOPTIME,
--IUT Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!IUT') as IUTsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!IUT') as IUTAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!IUT') as IUTRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_To_OR!IUT') as IUTTIME,
--Exam under anesthesia Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Birth_Canal_Revision') as EUAsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Birth_Canal_Revision') as EUAAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Birth_Canal_Revision') as EUARES,
(Select top 1 f.ValueTime
from BVFindings f
where 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.sessionid

LEFT OUTER JOIN DatamartDB2.dbo.BVReports BVReports on
BLSession_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 on
BLSession_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

  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000