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)
 account for nulls

Author  Topic 

sharona
Yak Posting Veteran

75 Posts
Posted - 2011-05-23 : 16:15:38
I have the following stored procedure where i convert a varchar to a datetime. when i run for specific dates i can not convert the date and i get an error message in my reports. how do i account for null value in the red items


CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
clientdocguid numeric(16,0),
ClientVisitGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
DOB DATETIME,
GENDER VARCHAR(15),
LANGUAGECDE varchar(20),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
Unit_Note_Written VARCHAR(30),
Prim_diag_description VARCHAR(60),
MD_DISC INT,
SW_DISC INT,
NP_DISC INT,
INIT_DATE_OF_RQST Datetime,
INIT_NOTE_DATE varchar(255),
INIT_NOTE_TIME varchar(255),
REFERRING_PROVIDER varchar(255),
REFERRING_SERVICE varchar(2000),
INIT_SUPPORT_COUNSELING varchar(2000),
INIT_FAMILY_SUPPORT VARCHAR(2000),
INIT_HOSPTICE_REF_DISCUSS VARCHAR(2000),
INIT_PROGNOSTICATION VARCHAR(2000),
INIT_PAIN_MGMT VARCHAR(2000),
INIT_SYMPTOM_MGMT VARCHAR(2000),
INIT_WITHHOLD_SUSTAIN VARCHAR(2000),
INIT_DISCHARGE_PLAN VARCHAR(2000),
INIT_GOALS_CARE VARCHAR(2000),
INIT_ADVANCE_DIRECTIVE VARCHAR(2000),
INIT_PATIENT_SUPPORT VARCHAR(2000),
INIT_OTHER VARCHAR(2000),
FLUP_DATE VARCHAR(255),
LOS_ADMIT INT,
LOS_NOTE INT,
FLUP_TIME VARCHAR(255),
FLUP_PAIN_MGMT VARCHAR(2000),
FLUP_SYMPTOM_MGMT VARCHAR(2000),
FLUP_DECISION_MAKING VARCHAR(2000),
FLUP_GOALS_CARE VARCHAR(2000),
FLUP_PAT_FAM_SUP VARCHAR(2000),
FLUP_PROGNOSIS VARCHAR(2000),
FLUP_WITHDRAWAL_LIFE VARCHAR(2000),
FLUP_DISCHARGE_PLAN VARCHAR(2000),
FLUP_HLTH_CARE_AGENT VARCHAR(2000),
FLUP_ADVANCE_DIRECTIVE VARCHAR(2000),
FLUP_HOSPTICE_REF_DISCUSS VARCHAR(2000),
FLUP_EDU_SUP_STAFF VARCHAR(2000),
FLUP_FAM_CONF VARCHAR(2000),
FLUP_CODE_STATUS VARCHAR(2000),
FLUP_HOSPTICE_REF VARCHAR(2000))



--Get all patients who had a PALLIATIVE CARE CONSULT 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,
cd.GUID as ClientDocGUID,
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,
obsCatalog.Name as cat_name,
ISNULL(obser.ValueText, obsFS.Value) AS Value_TEXT,
dbo.CV3HealthIssueDeclaration.text as Prim_diag_description,
CASE WHEN ISDATE (rtrim(convert(char(2),c.BirthMonthNum))+'/'+ rtrim(convert(char(2),c.BirthDayNum))+'/'+
rtrim(convert(char(4),c.BirthYearNum))) =1 THEN
convert(char(2),c.BirthMonthNum) +'/'+ rtrim(convert(char(2),c.BirthDayNum))+'/'+
convert(char(4),c.BirthYearNum) ELSE null end DOB,
c.gendercode AS GENDER,
c.languagecode AS LANGUAGECDE,
Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (CD.clientVisitGUID,obserdoc.RecordedDtm),
sum(x.MD_DISC) as MD_DISC,
sum(x.SW_DISC) AS SW_DISC,
sum(x.NP_DISC) AS NP_DISC

into #result

FROM
dbo.CV3ClientVisit AS cv WITH (nolock)
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID and cd.PatCareDocGUID IN ('9000001693102020', '9000001693202020')
INNER JOIN
dbo.CV3ObservationDocument AS obserdoc WITH (nolock) ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN
dbo.CV3Observation AS obser WITH (nolock) ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem WITH (nolock) ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog WITH (nolock) ON obsCatalog.GUID = obscatitem.MasterObsGUID
INNER JOIN
dbo.CV3Client AS c WITH (nolock) ON cv.ClientGUID = c.GUID
INNER JOIN
dbo.CV3HealthIssueDeclaration WITH (nolock) ON cv.GUID = dbo.CV3HealthIssueDeclaration.ClientVisitGUID AND
cv.ClientGUID = dbo.CV3HealthIssueDeclaration.ClientGUID AND dbo.CV3HealthIssueDeclaration.TypeCode = 'Admitting Dx' AND
dbo.CV3HealthIssueDeclaration.Status = 'active'
LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID
INNER JOIN
(SELECT
GUID AS GUID,
--displayname AS NAME,
--OccupationCode AS user_occ,
CASE WHEN OccupationCode='MD' THEN 1 END AS MD_DISC,
CASE WHEN OccupationCode='Senior Social Worker'then 1 END AS SW_DISC,
CASE WHEN OccupationCode='NP' THEN 1 END AS NP_DISC
FROM
dbo.CV3User WITH (nolock)
where OccupationCode IN ('MD','Senior Social Worker','NP')) as x
ON x.GUID = cd.UserGUID

WHERE
(loc.ParentGUID IN ('2000001000061001', '4000001145061001 ','8000001069061001'))
AND (cv.TypeCode = 'Inpatient')
AND cv.AdmitDtm BETWEEN @start_date AND @end_date

GROUP BY
loc.ParentGUID,
cv.GUID,
cd.guid,
cv.IDCode,
cv.VisitIDCode,
cv.ClientDisplayName,
c.BirthMonthNum,
c.BirthDayNum,
c.BirthYearNum,
c.gendercode,
c.languagecode,
cv.AdmitDtm,
cv.DischargeDtm,
obsCatalog.Name,
CD.clientVisitGUID,
obserdoc.RecordedDtm,
obser.ValueText,
obsFS.Value,
dbo.CV3HealthIssueDeclaration.text

--Add all data to the final table

INSERT #Final
( Site ,
Campus,
clientdocguid,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
Prim_diag_description,
MD_DISC,
SW_DISC,
NP_DISC)

SELECT DISTINCT
Site ,
Campus,
clientdocguid,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
Prim_diag_description,
MD_DISC,
SW_DISC,
NP_DISC


FROM #Result

-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET INIT_DATE_OF_RQST = R.value_text
FROM #Result r, #Final f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_ReqDate_FT'

UPDATE #FINAL
SET INIT_NOTE_DATE = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Date_ft'

UPDATE #FINAL
SET INIT_NOTE_TIME = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Time_ft'
UPDATE #FINAL
SET REFERRING_PROVIDER = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_RefMD_ft'

UPDATE #FINAL
SET REFERRING_SERVICE = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_RefSvc_FT'

UPDATE #FINAL
SET INIT_SUPPORT_COUNSELING = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Supportive counseling'

UPDATE #FINAL
SET INIT_FAMILY_SUPPORT = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Family support'

UPDATE #FINAL
SET INIT_HOSPTICE_REF_DISCUSS = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Hospice Referral Discussion'

UPDATE #FINAL
SET INIT_PROGNOSTICATION= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Prognostication'

UPDATE #FINAL
SET INIT_PAIN_MGMT= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Pain management'

UPDATE #FINAL
SET INIT_SYMPTOM_MGMT = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Symptom management'

UPDATE #FINAL
SET INIT_WITHHOLD_SUSTAIN= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Withholding or withdrawing of life sustaining treatments'

UPDATE #FINAL
SET INIT_DISCHARGE_PLAN = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Discharge planning'

UPDATE #FINAL
SET INIT_GOALS_CARE = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Goals of care discussion'

UPDATE #FINAL
SET INIT_ADVANCE_DIRECTIVE = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Advance Directive discussion:'

UPDATE #FINAL
SET INIT_PATIENT_SUPPORT= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason1.1' AND r.Value_text='Patient Support:'

UPDATE #FINAL
SET INIT_OTHER = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_Reason1.2' AND r.Value_text='Other'

UPDATE #Final
SET FLUP_DATE = R.value_text
FROM #Result r, #Final f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_FU_Date_ft'

UPDATE #Final
SET FLUP_TIME = R.value_text
FROM #Result r, #Final f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_FU_Time_ft'

UPDATE #FINAL
SET FLUP_PAIN_MGMT = '1'
FROM #Result r, #Final f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_PainMgmt_ft'

UPDATE #FINAL
SET FLUP_SYMPTOM_MGMT = '1'
FROM #Result r, #Final f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_SympMgmt_ft'

UPDATE #FINAL
SET FLUP_DECISION_MAKING = '1'
FROM #Result r, #Final f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_Decision_ft'

UPDATE #FINAL
SET FLUP_GOALS_CARE= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_Goals_ft'

UPDATE #FINAL
SET FLUP_PAT_FAM_SUP= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_PtFam_ft'

UPDATE #FINAL
SET FLUP_PROGNOSIS= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_Prog_ft'

UPDATE #FINAL
SET FLUP_WITHDRAWAL_LIFE= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_Interven_ft'

UPDATE #FINAL
SET FLUP_DISCHARGE_PLAN = '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_DC_ft'

UPDATE #FINAL
SET FLUP_HLTH_CARE_AGENT= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_HCagent_ft'

UPDATE #FINAL
SET FLUP_ADVANCE_DIRECTIVE = '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_advdirgen_ft'

UPDATE #FINAL
SET FLUP_HOSPTICE_REF_DISCUSS= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_hosprefdis_ft'

UPDATE #FINAL
SET FLUP_EDU_SUP_STAFF = '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_edu_ft'

UPDATE #FINAL
SET FLUP_FAM_CONF= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_famconf_ft'

UPDATE #FINAL
SET FLUP_CODE_STATUS = '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_codeclar_ft'

UPDATE #FINAL
SET FLUP_HOSPTICE_REF= '1'
FROM #Result r, #FINAL f
WHERE f.ClientVisitGUID = r.ClientVisitGUID AND r.cat_name = 'PalCare_InitCons_PC_Hospice_ft'

--Final select

DELETE FROM #FINAL where INIT_DATE_OF_RQST IS NULL and INIT_NOTE_DATE IS NULL
AND INIT_NOTE_TIME IS NULL AND REFERRING_PROVIDER IS NULL AND REFERRING_SERVICE IS NULL
AND INIT_SUPPORT_COUNSELING IS NULL AND INIT_FAMILY_SUPPORT IS NULL AND INIT_HOSPTICE_REF_DISCUSS IS NULL
AND INIT_PROGNOSTICATION IS NULL AND INIT_PAIN_MGMT IS NULL AND INIT_SYMPTOM_MGMT IS NULL
AND INIT_WITHHOLD_SUSTAIN IS NULL AND INIT_DISCHARGE_PLAN IS NULL AND INIT_GOALS_CARE IS NULL
AND INIT_ADVANCE_DIRECTIVE IS NULL AND INIT_PATIENT_SUPPORT IS NULL AND INIT_OTHER IS NULL
and FLUP_PAIN_MGMT is null and FLUP_SYMPTOM_MGMT is null and FLUP_DECISION_MAKING is null and
FLUP_GOALS_CARE is null and FLUP_PAT_FAM_SUP is null and FLUP_PROGNOSIS is null and FLUP_WITHDRAWAL_LIFE is null
and FLUP_DISCHARGE_PLAN is null and FLUP_HLTH_CARE_AGENT is null and FLUP_ADVANCE_DIRECTIVE is null and
FLUP_HOSPTICE_REF_DISCUSS is null and FLUP_EDU_SUP_STAFF is null and FLUP_FAM_CONF is null and FLUP_CODE_STATUS is null
and FLUP_HOSPTICE_REF is null


select distinct
Site,
Campus,
clientdocguid,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
Prim_diag_description,
sum(MD_DISC) as MD_DISC,
sum(SW_DISC) AS SW_DISC,
sum(NP_DISC) AS NP_DISC,
INIT_DATE_OF_RQST,
INIT_NOTE_DATE=CAST( CAST(INIT_NOTE_DATE as varchar(255)) AS datetime),
INIT_NOTE_TIME,
REFERRING_PROVIDER,
REFERRING_SERVICE,
INIT_SUPPORT_COUNSELING,
INIT_FAMILY_SUPPORT,
INIT_HOSPTICE_REF_DISCUSS,
INIT_PROGNOSTICATION,
INIT_PAIN_MGMT,
INIT_SYMPTOM_MGMT,
INIT_WITHHOLD_SUSTAIN,
INIT_DISCHARGE_PLAN ,
INIT_GOALS_CARE,
INIT_ADVANCE_DIRECTIVE,
INIT_PATIENT_SUPPORT,
INIT_OTHER,
FLUP_DATE= convert(varchar(7),FLUP_DATE, 102),
LOS_ADMIT=(DATEDIFF(dd,ADMIT_DATE, INIT_DATE_OF_RQST)),
LOS_NOTE=(DATEDIFF(dd,INIT_DATE_OF_RQST,INIT_NOTE_DATE)),
FLUP_TIME,
FLUP_PAIN_MGMT,
FLUP_SYMPTOM_MGMT,
FLUP_DECISION_MAKING,
FLUP_GOALS_CARE,
FLUP_PAT_FAM_SUP,
FLUP_PROGNOSIS,
FLUP_WITHDRAWAL_LIFE,
FLUP_DISCHARGE_PLAN,
FLUP_HLTH_CARE_AGENT,
FLUP_ADVANCE_DIRECTIVE,
FLUP_HOSPTICE_REF_DISCUSS,
FLUP_EDU_SUP_STAFF,
FLUP_FAM_CONF,
FLUP_CODE_STATUS,
FLUP_HOSPTICE_REF


from #FINAL
group by
Site,
Campus,
clientdocguid,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
Prim_diag_description,
INIT_DATE_OF_RQST,
INIT_NOTE_DATE,
INIT_NOTE_TIME,
REFERRING_PROVIDER,
REFERRING_SERVICE,
INIT_SUPPORT_COUNSELING,
INIT_FAMILY_SUPPORT,
INIT_HOSPTICE_REF_DISCUSS,
INIT_PROGNOSTICATION,
INIT_PAIN_MGMT,
INIT_SYMPTOM_MGMT,
INIT_WITHHOLD_SUSTAIN,
INIT_DISCHARGE_PLAN ,
INIT_GOALS_CARE,
INIT_ADVANCE_DIRECTIVE,
INIT_PATIENT_SUPPORT,
INIT_OTHER,
FLUP_DATE,
LOS_ADMIT,
LOS_NOTE,
FLUP_TIME,
FLUP_PAIN_MGMT,
FLUP_SYMPTOM_MGMT,
FLUP_DECISION_MAKING,
FLUP_GOALS_CARE,
FLUP_PAT_FAM_SUP,
FLUP_PROGNOSIS,
FLUP_WITHDRAWAL_LIFE,
FLUP_DISCHARGE_PLAN,
FLUP_HLTH_CARE_AGENT,
FLUP_ADVANCE_DIRECTIVE,
FLUP_HOSPTICE_REF_DISCUSS,
FLUP_EDU_SUP_STAFF,
FLUP_FAM_CONF,
FLUP_CODE_STATUS,
FLUP_HOSPTICE_REF

DROP TABLE #Result
DROP TABLE #Final


END
go
IF OBJECT_ID('dbo.NYP_pal_care_rpt') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.NYP_pal_care_rpt >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.NYP_pal_care_rpt >>>'
go


GRANT EXECUTE ON dbo.NYP_pal_care_rpt TO public;
GRANT EXECUTE ON dbo.NYP_pal_care_rpt TO crystal
GO


SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-05-23 : 16:37:43
Try the COALESCE function:
http://msdn.microsoft.com/en-us/library/ms190349.aspx
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site