vwNBOCAP_FULL_CHEMORADIOTHERAPIES

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

NHSNumber

varchar

√

null

DateOfBirth

datetime

16,3

√

null

PreOpInitialProviderOrganisation

varchar

√

null

PreOpInitialCancerTreatmentModality

varchar

√

null

PostOpProviderOrganisation

varchar

√

null

PostOpCancerTreatmentModality

varchar

√

null

N4_1_DIAGNOSIS_DATE

smalldatetime

√

null

View Definition

-- Created by Paul Stevens, February 2014
-- Amended by Paul Stevens, April 2014
-- Amended by Paul Stevens, June 2014

CREATE VIEW [dbo].[vwNBOCAP_FULL_CHEMORADIOTHERAPIES]
AS

	SELECT	mr.CARE_ID,
			d.N1_1_NHS_NUMBER AS NHSNumber,
			d.N1_10_DATE_BIRTH AS DateOfBirth,
			CASE
			WHEN nbcpre.EVENT_TYPE = 'CHEMO'
			THEN mcpre.N9_1_SITE_CODE			
			WHEN nbcpre.EVENT_TYPE = 'TELE'
			THEN mtpre.N10_1_SITE_CODE			
			WHEN nbcpre.EVENT_TYPE = 'BRACHY'
			THEN mbpre.N11_1_SITE_CODE
			ELSE NULL			
			END PreOpInitialProviderOrganisation,
			CASE
			WHEN nbcpre.EVENT_TYPE = 'CHEMO' AND (mcpre.N_CHEMORADIO <> '1' OR mcpre.N_CHEMORADIO IS NULL)
			THEN CAST(ISNULL('02','') As varchar(3))
			WHEN nbcpre.EVENT_TYPE = 'CHEMO' AND mcpre.N_CHEMORADIO = '1'
			THEN CAST(ISNULL('04','') As varchar(3))
			WHEN nbcpre.EVENT_TYPE = 'TELE' AND (mtpre.N_CHEMORADIO <> '1' OR mtpre.N_CHEMORADIO IS NULL)
			THEN CAST(ISNULL('05','') As varchar(3))
			WHEN nbcpre.EVENT_TYPE = 'TELE' AND mtpre.N_CHEMORADIO = '1'
			THEN CAST(ISNULL('04','') As varchar(3))
			WHEN nbcpre.EVENT_TYPE = 'BRACHY'
			THEN CAST(ISNULL('06','') As varchar(3))
			ELSE NULL
			END PreOpInitialCancerTreatmentModality,
			CASE			
			WHEN nbcpost.EVENT_TYPE = 'CHEMO' 
			THEN mcpost.N9_1_SITE_CODE			
			WHEN nbcpost.EVENT_TYPE = 'TELE'
			THEN mtpost.N10_1_SITE_CODE			
			WHEN nbcpost.EVENT_TYPE = 'BRACHY'
			THEN mbpost.N11_1_SITE_CODE
			ELSE NULL			
			END PostOpProviderOrganisation,
			CASE
			WHEN nbcpost.EVENT_TYPE = 'CHEMO' AND (mcpost.N_CHEMORADIO <> '1' OR mcpost.N_CHEMORADIO IS NULL)
			THEN CAST(ISNULL('02','') As varchar(3))
			WHEN nbcpost.EVENT_TYPE = 'CHEMO' AND mcpost.N_CHEMORADIO = '1'
			THEN CAST(ISNULL('04','') As varchar(3))
			WHEN nbcpost.EVENT_TYPE = 'TELE' AND (mtpost.N_CHEMORADIO <> '1' OR mtpost.N_CHEMORADIO IS NULL)
			THEN CAST(ISNULL('05','') As varchar(3))
			WHEN nbcpost.EVENT_TYPE = 'TELE' AND mtpost.N_CHEMORADIO = '1'
			THEN CAST(ISNULL('04','') As varchar(3))
			WHEN nbcpost.EVENT_TYPE = 'BRACHY'
			THEN CAST(ISNULL('06','') As varchar(3))
			ELSE NULL
			END PostOpCancerTreatmentModality,
			mr.N4_1_DIAGNOSIS_DATE
	FROM	tblMAIN_REFERRALS mr LEFT JOIN vwNBOCAP_BASE_CHEMORADIOTHERAPIES_PREOP nbcpre
			ON mr.CARE_ID = nbcpre.CARE_ID						
			LEFT JOIN tblMAIN_CHEMOTHERAPY mcpre
			ON nbcpre.EVENT_ID = mcpre.CHEMO_ID		
			LEFT JOIN tblMAIN_TELETHERAPY mtpre
			ON nbcpre.EVENT_ID = mtpre.TELE_ID			
			LEFT JOIN tblMAIN_BRACHYTHERAPY mbpre
			ON nbcpre.EVENT_ID = mbpre.BRACHY_ID
			LEFT JOIN vwNBOCAP_BASE_CHEMORADIOTHERAPIES_POSTOP nbcpost
			ON mr.CARE_ID = nbcpost.CARE_ID			
			LEFT JOIN tblMAIN_CHEMOTHERAPY mcpost
			ON nbcpost.EVENT_ID = mcpost.CHEMO_ID			
			LEFT JOIN tblMAIN_TELETHERAPY mtpost
			ON nbcpost.EVENT_ID = mtpost.TELE_ID			
			LEFT JOIN tblMAIN_BRACHYTHERAPY mbpost
			ON nbcpost.EVENT_ID = mbpost.BRACHY_ID
			LEFT JOIN tblDEMOGRAPHICS d
			ON mr.PATIENT_ID = d.PATIENT_ID
			LEFT JOIN vwNBOCAP_FULL_TUMOURS nftpre
			ON nbcpre.CARE_ID = nftpre.CARE_ID
			LEFT JOIN vwNBOCAP_FULL_TUMOURS nftpost
			ON nbcpost.CARE_ID = nftpost.CARE_ID
	WHERE	(nbcpre.EVENT_TYPE IN ('CHEMO','TELE','BRACHY') AND
			dbo.IsNSTSValid(d.PATIENT_ID) = 1 AND
			ISDATE(d.N1_10_DATE_BIRTH) = 1 AND YEAR(d.N1_10_DATE_BIRTH) >= 1900 AND
			nftpre.OrganisationFirstSeen IS NOT NULL AND
			nftpre.DateOfDiagnosis IS NOT NULL AND
			nftpre.SourceOfReferral IS NOT NULL AND			
			nftpre.PerformanceStatus IS NOT NULL AND			
			--((nft.CarePlanIntent IN ('C','Z') AND nft.PlannedCancerTreatmentType IS NOT NULL) OR			
			--(nft.CarePlanIntent = 'X' AND nft.NoCancerTreatmentReason IS NOT NULL)) AND
			((nftpre.CarePlanIntent IN ('C','Z') AND nftpre.PlannedCancerTreatmentType IS NOT NULL) OR			
			(nftpre.CarePlanIntent = 'X' AND nftpre.NoCancerTreatmentReason IS NOT NULL) OR
			nftpre.CarePlanIntent IS NOT NULL) AND  
			nftpre.TCategory IS NOT NULL AND
			nftpre.NCategory IS NOT NULL AND
			nftpre.MCategory IS NOT NULL
			AND (dbo.IsNSTSValid(mr.PATIENT_ID)) = 1
			AND ISDATE(d.N1_10_DATE_BIRTH) = 1
			AND NULLIF(d.N1_8_POSTCODE,' ') IS NOT NULL
			AND d.N1_9_SEX IN (0,1,2,9))
			
			OR
			
			(nbcpost.EVENT_TYPE IN ('CHEMO','TELE','BRACHY') AND
			dbo.IsNSTSValid(d.PATIENT_ID) = 1 AND
			ISDATE(d.N1_10_DATE_BIRTH) = 1 AND YEAR(d.N1_10_DATE_BIRTH) >= 1900 AND
			nftpost.OrganisationFirstSeen IS NOT NULL AND
			nftpost.DateOfDiagnosis IS NOT NULL AND
			nftpost.SourceOfReferral IS NOT NULL AND			
			nftpost.PerformanceStatus IS NOT NULL AND			
			--((nft.CarePlanIntent IN ('C','Z') AND nft.PlannedCancerTreatmentType IS NOT NULL) OR			
			--(nft.CarePlanIntent = 'X' AND nft.NoCancerTreatmentReason IS NOT NULL)) AND
			((nftpost.CarePlanIntent IN ('C','Z') AND nftpost.PlannedCancerTreatmentType IS NOT NULL) OR			
			(nftpost.CarePlanIntent = 'X' AND nftpost.NoCancerTreatmentReason IS NOT NULL) OR
			nftpost.CarePlanIntent IS NOT NULL) AND  
			nftpost.TCategory IS NOT NULL AND
			nftpost.NCategory IS NOT NULL AND
			nftpost.MCategory IS NOT NULL
			AND (dbo.IsNSTSValid(mr.PATIENT_ID)) = 1
			AND ISDATE(d.N1_10_DATE_BIRTH) = 1
			AND NULLIF(d.N1_8_POSTCODE,' ') IS NOT NULL
			AND d.N1_9_SEX IN (0,1,2,9))

Columns

View Definition

Possibly Referenced Tables/Views