vwNBOCAP_CHEMOS_PREOP_CONCATENATIONS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

NHSNumber

varchar

√

null

CARE_ID

int

null

EVENT_DATE

smalldatetime

√

null

EVENT_ID

int

null

PreOpInitialProviderOrganisation

varchar

√

null

PreOpInitialCancerTreatmentModality

varchar

√

null

PostOpProviderOrganisation

int

√

null

PostOpCancerTreatmentModality

int

√

null

View Definition

-- Created by Paul Stevens, February 2014

CREATE VIEW [dbo].[vwNBOCAP_CHEMOS_PREOP_CONCATENATIONS]
AS

SELECT DISTINCT
d.N1_1_NHS_NUMBER AS NHSNumber,
ncns.CARE_ID,
ncns.EVENT_DATE,
ncns.EVENT_ID,
CASE
WHEN ncns.EVENT_TYPE = 'CHEMO'
THEN mc.N9_1_SITE_CODE
WHEN ncns.EVENT_TYPE = 'TELE'
THEN mt.N10_1_SITE_CODE
WHEN ncns.EVENT_TYPE = 'BRACHY'
THEN mb.N11_1_SITE_CODE
ELSE NULL
END PreOpInitialProviderOrganisation,
CASE
WHEN ncns.EVENT_TYPE = 'CHEMO' AND (mc.N_CHEMORADIO <> '1' OR mc.N_CHEMORADIO IS NULL)
THEN CAST(ISNULL('02','') As varchar(3))
WHEN ncns.EVENT_TYPE = 'CHEMO' AND mc.N_CHEMORADIO = '1'
THEN CAST(ISNULL('04','') As varchar(3))
WHEN ncns.EVENT_TYPE = 'TELE' AND (mt.N_CHEMORADIO <> '1' OR mt.N_CHEMORADIO IS NULL)
THEN CAST(ISNULL('05','') As varchar(3))
WHEN ncns.EVENT_TYPE = 'TELE' AND mt.N_CHEMORADIO = '1'
THEN CAST(ISNULL('04','') As varchar(3))
WHEN ncns.EVENT_TYPE = 'BRACHY'
THEN CAST(ISNULL('06','') As varchar(3))
ELSE NULL
END PreOpInitialCancerTreatmentModality,
NULL AS PostOpProviderOrganisation,
NULL AS PostOpCancerTreatmentModality
FROM 
vwNBOCAP_CHEMORADIOTHERAPIES_NO_SURGERIES ncns 
LEFT JOIN tblMAIN_CHEMOTHERAPY mc
ON ncns.CARE_ID = mc.CARE_ID AND ncns.EVENT_ID = mc.CHEMO_ID
LEFT JOIN tblMAIN_TELETHERAPY mt
ON ncns.CARE_ID = mt.CARE_ID AND ncns.EVENT_ID = mt.TELE_ID
LEFT JOIN tblMAIN_BRACHYTHERAPY mb
ON ncns.CARE_ID = mb.CARE_ID AND ncns.EVENT_ID = mb.BRACHY_ID
LEFT JOIN tblMAIN_REFERRALS mr
ON ncns.CARE_ID = mr.CARE_ID
LEFT JOIN tblDEMOGRAPHICS d
ON mr.PATIENT_ID = d.PATIENT_ID

Columns

View Definition

Possibly Referenced Tables/Views