HOW TO PASS NULL - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

HOW TO PASS NULL

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-01 : 12:58:22

I have a stored procedure where i use a case statment to create buckets by a guid. if the client doesnt have the specific doc guid i would still like to see all the buckets and place null in the value of the bucket, how would i do that?

SELECT DISTINCT
SITE = 'WEST',
CV.IDCode AS MRN,
CV.VisitIDCode AS EncNumber,
CV.ClientDisplayName AS PATIENT,
CV.AdmitDtm AS ADMIT_DATE,
CV.DischargeDtm AS DISCHARGE_DATE,
Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,OD.RecordedDtm),
CV.CurrentLocation AS CURRENT_LOCATION_TIME_RPT,
CD.DocumentName AS DOC_NAME,
OBC.GUID AS OBS_GUID,
OBC.Name AS OBS_DOC_NAME,
CASE WHEN OBC.GUID = '9000010604202900' THEN 'AIRWAY BREATHING'
WHEN OBC.GUID = '9000010604302900' THEN 'IV FLUIDS'
WHEN OBC.GUID = '9000010604402900'THEN 'BLOOD PRODUCTS'
WHEN OBC.GUID = '9000010604502900' THEN 'MEDICATIONS'
WHEN OBC.GUID = '9000010604602900' THEN 'LABORATORY TESTS'
WHEN OBC.GUID = '9000010604702900' THEN 'RADIOLOGY EXAMS'
WHEN OBC.GUID = '9000010604802900' THEN 'IV LINES'
WHEN OBC.GUID = '9000010604902900' THEN 'OTHER'
WHEN OBC.GUID = '9000010605602900' THEN 'PATIENT LOCATION'
WHEN OBC.GUID = '9000010609302900' THEN 'RRT CALLED' END AS RECOMENDATION,
ISNULL(OB.ValueText, SCMO.Value) AS Value_text,
CD.AuthoredDtm AS AUTHOR_DATE,
OD.RecordedDtm AS RECORD_DATE

FROM CV3Client AS CL WITH (nolock) INNER JOIN
CV3ClientID AS CID WITH (nolock) ON CL.GUID = CID.ClientGUID INNER JOIN
CV3ClientDocument AS CD WITH (nolock) ON CD.ClientGUID = CID.ClientGUID
and CD.PatCareDocGUID = '9000001709102020'
INNER JOIN
CV3ClientVisit AS CV WITH (nolock) ON CV.GUID = CD.ClientVisitGUID
AND CID.ClientGUID = CV.ClientGUID INNER JOIN
CV3Location AS Loc ON CV.CurrentLocationGUID = loc.GUID
and loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
INNER JOIN
CV3ObservationDocument AS OD WITH (nolock) ON OD.OwnerGUID = CD.GUID
INNER JOIN
CV3Observation AS OB WITH (nolock) ON OB.GUID = OD.ObservationGUID INNER JOIN
CV3ObsCatalogItem AS OBC WITH (nolock) ON OBC.GUID = OB.ObsItemGUID
AND OBC.GUID in
('9000010604202900',--AIRWAY BREATHING note_rapid_resp_recommendations_ft1
'9000010604302900',--IV FLUIDS note_rapid_resp_recommendations_ft2
'9000010604402900',--BLOOD PRODUCTS note_rapid_resp_recommendations_ft3
'9000010604502900',--MEDICATIONS note_rapid_resp_recommendations_ft4
'9000010604602900',--LABORATORY TESTS note_rapid_resp_recommendations_ft5
'9000010604702900',--RADIOLOGY EXAMS note_rapid_resp_recommendations_ft6
'9000010604802900',--IV LINES note_rapid_resp_recommendations_ft7
'9000010604902900',--OTHER note_rapid_resp_recommendations_ft8
'9000010605602900',--PATIENT LOCATION note_rapid_resp_pt_loc
'9000010609302900')--RRT CALLED note_rapid_resp_outcomes_ft3
INNER JOIN
CV3ObsCatalogMasterItem AS OBCI WITH (nolock) ON OBCI.GUID = OBC.MasterObsGUID LEFT OUTER JOIN
SCMObsFSListValues AS SCMO WITH (nolock) ON SCMO.ParentGUID = OD.ObservationDocumentGUID

WHERE CONVERT(datetime,CONVERT(char(10),CD.AuthoredDtm,101))
>= CONVERT(datetime,CONVERT(char(10),'12/01/2010',101))
and CONVERT(datetime,CONVERT(char(10),CD.AuthoredDtm,101))
<= CONVERT(datetime,CONVERT(char(10),'12/31/2010',101)) AND CV.IDCode ='539 15 63'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-01 : 15:26:29

WHEN OBC.GUID IS NULL THEN 'it''s null'

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-02 : 12:00:54

what i ended up doing was the following
i created 2 temp tables, then did an update to the final table so it inserted as 1 row per patient and doesnt pull null rows.

DECLARE
@start_date datetime,
@end_date datetime

--use convert to mm/dd/yyyy with default Start_time 12:00:00 and default End_time 11:59:59
SELECT @start_date = convert(datetime,convert(varchar(10), @StartDate, 101))
SELECT @end_date = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @EndDate, 101))))

CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
ClientVisitGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
Unit_Note_Written VARCHAR(30),
LOC_time_rpt varchar(30),
LOC_NAME VARCHAR(60),
ClientDocGUID numeric(16,0),
Value_text VARCHAR(2000),
AUTHOR_DATE DATETIME,
AIRWAY_BREATHING varchar(2000),
IV_FLUIDS varchar(2000),
BLOOD_PRODUCTS varchar(2000),
MEDICATIONS varchar(2000),
LAB_TESTS varchar(2000),
RADIOLOGY_EXAMS VARCHAR(2000),
IV_LINES VARCHAR(2000),
OTHER VARCHAR(2000),
PATIENT_LOC VARCHAR(2000),
RRT_CALLED VARCHAR(2000)
)

--Get all patients who had a Rapid Response Team Responder Note within the select date time
SELECT DISTINCT
Site = 'WEST',
Campus =
CASE
when loc.ParentGUID = 2000001000061001 then 'Allen Hospital'
when loc.ParentGUID = 4000001145061001 then 'Milstein Hospital'
when loc.ParentGUID = 8000001069061001 then 'MS CHONY'
END,
cv.GUID AS ClientVisitGUID,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
cv.AdmitDtm AS ADMIT_DATE,
cv.DischargeDtm AS DISCHARGE_DATE,
Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,obserdoc.RecordedDtm),
CV.CurrentLocation AS LOC_TIME_RPT,
LOC.NAME AS LOC_NAME,
cd.GUID AS ClientDocGUID,
ISNULL(obser.ValueText, obsFS.Value) AS Value_TEXT,
cd.AuthoredDtm AS AUTHOR_DATE,
obsCatalog.Name

INTO #Result
FROM CV3ClientVisit cv
INNER JOIN cv3location loc ON cv.currentlocationguid = loc.guid
INNER JOIN CV3ClientDocument cd ON cv.GUID = cd.ClientVisitGUID
INNER JOIN CV3ObservationDocument obserdoc ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN CV3Observation obser ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN CV3ObsCatalogItem AS obscatitem ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN CV3ObsCatalogMasterItem AS obsCatalog ON obsCatalog.GUID = obscatitem.MasterObsGUID
LEFT OUTER JOIN SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID

WHERE cd.PatCareDocGUID = '9000001709102020'
--AND cv.TypeCode = 'Inpatient'
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
--AND obsCatalog.DataType = 8
AND obsCatalog.Name in ( 'note_rapid_resp_recommendations_ft5','note_rapid_resp_recommendations_ft1',
'note_rapid_resp_recommendations_ft3' ,'note_rapid_resp_recommendations_ft7',
'note_rapid_resp_recommendations_ft8','note_rapid_resp_outcomes_ft3' ,
'note_rapid_resp_recommendations_ft2','note_rapid_resp_recommendations_ft4' ,
'note_rapid_resp_recommendations_ft6' , 'note_rapid_resp_pt_loc')
--AND cd.IsIncomplete = 0
AND cd.AuthoredDtm BETWEEN @start_date AND @end_date
ORDER BY cv.GUID

--Add all data to the final table
INSERT #Final
( Site ,
Campus,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
ClientDocGUID,
AUTHOR_DATE
)
SELECT
Site ,
Campus,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
ClientDocGUID,
AUTHOR_DATE
FROM #Result

-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET AIRWAY_BREATHING = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft1'

UPDATE #Final
SET IV_FLUIDS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft2'

UPDATE #Final
SET BLOOD_PRODUCTS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft3'

UPDATE #Final
SET MEDICATIONS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft4'

UPDATE #Final
SET LAB_TESTS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft5'

UPDATE #Final
SET RADIOLOGY_EXAMS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft6'

UPDATE #Final
SET IV_LINES = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft7'

UPDATE #Final
SET OTHER = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft8'

UPDATE #Final
SET PATIENT_LOC = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_pt_loc'

UPDATE #Final
SET RRT_CALLED = r.Value_text
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_outcomes_ft3'

DELETE FROM #Final
where (AIRWAY_BREATHING IS NULL AND IV_FLUIDS IS NULL AND BLOOD_PRODUCTS IS NULL AND MEDICATIONS IS NULL
AND LAB_TESTS IS NULL AND RADIOLOGY_EXAMS IS NULL AND IV_LINES IS NULL AND OTHER IS NULL AND PATIENT_LOC IS NULL AND RRT_CALLED IS NULL)

--Final select
SELECT DISTINCT
Site ,
Campus,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
ClientDocGUID,
AUTHOR_DATE,
AIRWAY_BREATHING,
IV_FLUIDS,
BLOOD_PRODUCTS,
MEDICATIONS,
LAB_TESTS,
RADIOLOGY_EXAMS ,
IV_LINES,
OTHER,
PATIENT_LOC,
RRT_CALLED

FROM #Final
ORDER BY MRN

DROP TABLE #Result
DROP TABLE #Final

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources