vwPALLIATIVE_SUMMARY

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

PALLIATIVE_ID

int

null

PATIENT_ID

int

null

L_REG_DATE

smalldatetime

√

null

Referral_Desc

varchar

√

null

ReasonRef_Desc

varchar

√

null

Outcome_Desc

varchar

√

null

L_DATE_OF_FIRST_CONTACT

smalldatetime

√

null

L_CONSULTANT

varchar

√

null

CON_DESC

varchar

√

null

L_KEY_WORKER

varchar

√

null

L_DATE_DIAGNOSIS

smalldatetime

√

null

L_CANCER_SITE1

varchar

√

null

DIAG1

varchar

150

√

null

DIAG2

varchar

150

√

null

L_LOCAL

int

√

null

L_DATE_DISCHARGE

date

√

null

ADMITLOC_DESC

varchar

√

null

DischargeMethod_Desc

varchar

√

null

PALLIATIVE_REFERRAL_ID

int

√

null

PALLIATIVE_REFERRAL_DESC

varchar

√

null

View Definition

CREATE VIEW [dbo].[vwPALLIATIVE_SUMMARY]
AS
SELECT     dbo.tblPALLIATIVE_CARE.PALLIATIVE_ID, dbo.tblPALLIATIVE_CARE.PATIENT_ID, dbo.tblPALLIATIVE_CARE.L_REG_DATE, 
                      dbo.ltblPAL_REF_SOURCE.Referral_Desc, dbo.ltblPAL_REASON_REFERRAL.ReasonRef_Desc, dbo.ltblPAL_REF_OUTCOME.Outcome_Desc, 
                      dbo.tblPALLIATIVE_CARE.L_DATE_OF_FIRST_CONTACT, dbo.tblPALLIATIVE_CARE.L_CONSULTANT, dbo.ltblCONSULTANTS.CON_DESC, 
                      dbo.tblPALLIATIVE_CARE.L_KEY_WORKER, dbo.tblPALLIATIVE_CARE.L_DATE_DIAGNOSIS,                      
                      CASE                       
                      WHEN dbo.tblPALLIATIVE_CARE.PALLIATIVE_REFERRAL_ID = 4 
                      THEN dbo.tblPALLIATIVE_CARE.L_CANCER_SITE1
                      WHEN dbo.tblPALLIATIVE_CARE.PALLIATIVE_REFERRAL_ID IN (1,2,3) AND dbo.tblPALLIATIVE_CARE.DIAGNOSIS_TYPE_CODE_PRIMARY = 1                     
                      THEN dbo.tblPALLIATIVE_CARE.L_CANCER_SITE1                     
                      WHEN dbo.tblPALLIATIVE_CARE.PALLIATIVE_REFERRAL_ID IN (1,2,3) AND dbo.tblPALLIATIVE_CARE.DIAGNOSIS_TYPE_CODE_PRIMARY = 2
                      THEN 'Non Cancer Diagnosis'
                      ELSE NULL                      
                      END AS L_CANCER_SITE1,                      
                      ltblDIAGNOSIS_2.DIAG_DESC AS DIAG1, ltblDIAGNOSIS_1.DIAG_DESC AS DIAG2, 
                      dbo.tblPALLIATIVE_CARE.L_LOCAL, 
                      dbo.tblPALLIATIVE_CARE.L_DATE_DISCHARGE, dbo.ltblPAL_LOCATION_FROMTO.ADMITLOC_DESC, 
                      dbo.ltblPAL_DISCH_METHOD.DischargeMethod_Desc, dbo.tblPALLIATIVE_CARE.PALLIATIVE_REFERRAL_ID, dbo.ltblPALLIATIVE_REFERRAL_TYPE.PALLIATIVE_REFERRAL_DESC
FROM         dbo.ltblCONSULTANTS RIGHT OUTER JOIN
                      dbo.ltblPAL_REF_OUTCOME RIGHT OUTER JOIN
                      dbo.tblPALLIATIVE_CARE LEFT OUTER JOIN
                      dbo.ltblPAL_REASON_REFERRAL ON dbo.tblPALLIATIVE_CARE.L_REF_REASON = dbo.ltblPAL_REASON_REFERRAL.ReasonRef_ID ON 
                      dbo.ltblPAL_REF_OUTCOME.Outcome_ID = dbo.tblPALLIATIVE_CARE.L_REFERRAL_OUTCOME ON 
                      dbo.ltblCONSULTANTS.NATIONAL_CODE = dbo.tblPALLIATIVE_CARE.L_CONSULTANT AND dbo.ltblCONSULTANTS.IS_DELETED = 0
						LEFT OUTER JOIN
                      dbo.ltblPAL_DISCH_METHOD ON dbo.tblPALLIATIVE_CARE.L_DISCH_METHOD = dbo.ltblPAL_DISCH_METHOD.DischargeMethod_ID LEFT OUTER JOIN
                      dbo.ltblPAL_LOCATION_FROMTO ON dbo.tblPALLIATIVE_CARE.L_DISCH_DEST = dbo.ltblPAL_LOCATION_FROMTO.ADMITLOC_ID LEFT OUTER JOIN
                      dbo.ltblDIAGNOSIS ltblDIAGNOSIS_1 ON 
				      (CASE
							WHEN dbo.tblPALLIATIVE_CARE.L_OTHER_DIAGNOSIS_DET IS NOT NULL
								THEN dbo.tblPALLIATIVE_CARE.L_OTHER_DIAGNOSIS_DET
							WHEN dbo.tblPALLIATIVE_CARE.L_OTHER_DIAGNOSIS IS NOT NULL
								THEN dbo.tblPALLIATIVE_CARE.L_OTHER_DIAGNOSIS
						END) = ltblDIAGNOSIS_1.DIAG_CODE LEFT OUTER JOIN
                      dbo.ltblDIAGNOSIS ltblDIAGNOSIS_2 ON 
					  (CASE
							WHEN dbo.tblPALLIATIVE_CARE.L_PRIMARY_DIAGNOSIS_DET IS NOT NULL
								THEN dbo.tblPALLIATIVE_CARE.L_PRIMARY_DIAGNOSIS_DET
							WHEN dbo.tblPALLIATIVE_CARE.L_PRIMARY_DIAGNOSIS IS NOT NULL
								THEN dbo.tblPALLIATIVE_CARE.L_PRIMARY_DIAGNOSIS
						END) = ltblDIAGNOSIS_2.DIAG_CODE LEFT OUTER JOIN
                      dbo.ltblPAL_REF_SOURCE ON dbo.tblPALLIATIVE_CARE.L_REFERRAL_SOURCE = dbo.ltblPAL_REF_SOURCE.Referral_ID
								LEFT OUTER JOIN ltblPALLIATIVE_REFERRAL_TYPE ON
								dbo.tblPALLIATIVE_CARE.PALLIATIVE_REFERRAL_ID = dbo.ltblPALLIATIVE_REFERRAL_TYPE.PALLIATIVE_REFERRAL_ID

Columns

View Definition

Possibly Referenced Tables/Views