vwNBOCAP_FULL_TUMOURS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

NHSNumber

varchar

√

null

DateOfBirth

date

√

null

OrganisationFirstSeen

varchar

√

null

DateOfDiagnosis

smalldatetime

√

null

SourceOfReferral

varchar

√

null

ClinicalNurseSpecialistIndication

varchar

√

null

ICD10MajorSite

varchar

√

null

SynchronousCancer

varchar

8000

√

null

PerformanceStatus

int

√

null

CarePlanIntent

char

√

null

PlannedCancerTreatmentType

varchar

8000

√

null

NoCancerTreatmentReason

varchar

√

null

TCategory

varchar

√

null

NCategory

varchar

√

null

MCategory

varchar

√

null

MonitoringIntent

varchar

√

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_TUMOURS]
AS
	
	SELECT	nbt.CARE_ID AS CARE_ID,
			CASE
			WHEN LEN(ISNULL(nbt.N1_1_NHS_NUMBER,'')) = 10
			THEN nbt.N1_1_NHS_NUMBER 
			ELSE NULL
			END NHSNumber,
			nbt.N1_10_DATE_BIRTH AS DateOfBirth,
			CASE
			WHEN LEN(nbt.N1_3_ORG_CODE_SEEN) = 5
			THEN nbt.N1_3_ORG_CODE_SEEN
			ELSE NULL
			END OrganisationFirstSeen,
			CASE
			WHEN nbt.N4_1_DIAGNOSIS_DATE <= GETDATE() AND nbt.N4_1_DIAGNOSIS_DATE >= '01-JAN-00'
			THEN nbt.N4_1_DIAGNOSIS_DATE 
			ELSE NULL
			END DateOfDiagnosis,
			CASE 
			WHEN nbt.N2_16_OP_REFERRAL IN ('01','03','10','17','97')
			THEN nbt.N2_16_OP_REFERRAL
			WHEN nbt.N2_16_OP_REFERRAL = '04'
			THEN '01'
			WHEN nbt.N2_16_OP_REFERRAL IN ('05','06','07','11','13','14','15','16','92','93')
			THEN '97'						
			ELSE NULL
			END SourceOfReferral,
			nbt.L_INDICATOR_CODE AS ClinicalNurseSpecialistIndication,			
			CASE
			WHEN nbt.N4_2_DIAGNOSIS_CODE LIKE 'C18[0-7]' 
			THEN SUBSTRING(nbt.N4_2_DIAGNOSIS_CODE, 1, 3) + '.' + SUBSTRING(nbt.N4_2_DIAGNOSIS_CODE, 4, 1)
			WHEN nbt.N4_2_DIAGNOSIS_CODE IN ('C19X','C20X') 
			THEN SUBSTRING(nbt.N4_2_DIAGNOSIS_CODE, 1, 3)
			ELSE NULL
			END ICD10MajorSite,		
			CASE WHEN rc.R_SYNC_TUMOUR = '1' THEN	
			NULLIF(dbo.RemoveLastCharacter
            ((dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_APPENDIX,'Y','1'), 0), '1;', '') +
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_CAECUM,'Y','2'), 0), '2;', '') +
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_ASCENDING,'Y','3'), 0), '3;', '') + 
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_HEPATIC,'Y','4'), 0), '4;', '') +
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_TRANSVERSE,'Y','5'), 0), '5;', '') + 
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_SPLENIC,'Y','6'), 0), '6;', '') +
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_DESCENDING,'Y','7'), 0), '7;', '') +
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_SIGMOID,'Y','8'), 0), '8;', '') +
            dbo.IIF(ISNULL(REPLACE(rc.R_SYNC_RECTUM,'Y','9'), 0), '9;', ''))),'')			
			ELSE NULL
			END SynchronousCancer,
			CASE
			WHEN mcp.N5_10_WHO_STATUS IN ('0','1','2','3','4','9')
			THEN mcp.N5_10_WHO_STATUS
			ELSE NULL
			END PerformanceStatus,
			CASE
			WHEN mcp.N5_5_CARE_INTENT IN ('C','Z','X','9')
			THEN mcp.N5_5_CARE_INTENT
			ELSE NULL
			END CarePlanIntent,			
			 CASE WHEN ISNULL(mcp.N5_5_CARE_INTENT,'') <> 'X' AND ISNULL(mcp.N5_5_CARE_INTENT,'') IN ('C','Z')
			 THEN 
			 NULLIF(dbo.RemoveLastCharacter((CASE WHEN mcp.N5_6_TREATMENT_TYPE_1 LIKE '0[12356]'
                       THEN mcp.N5_6_TREATMENT_TYPE_1 + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_1 = '14'
                       THEN '02' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_1 = '13'
                       THEN '03' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_1 = '11'
                       THEN '05' + ';'                     
                       ELSE ''
                   END) +
                 (CASE WHEN mcp.N5_6_TREATMENT_TYPE_2 LIKE '0[12356]'
                       THEN mcp.N5_6_TREATMENT_TYPE_2 + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_2 = '14'
                       THEN '02' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_2 = '13'
                       THEN '03' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_2 = '11'
                       THEN '05' + ';'                       
                       ELSE ''
                   END) +
                 (CASE WHEN mcp.N5_6_TREATMENT_TYPE_3 LIKE '0[12356]'
                       THEN mcp.N5_6_TREATMENT_TYPE_3 + ';'  
                       WHEN mcp.N5_6_TREATMENT_TYPE_3 = '14'
                       THEN '02' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_3 = '13'
                       THEN '03' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_3 = '11'
                       THEN '05' + ';'                     
                       ELSE ''
                   END) +
                 (CASE WHEN mcp.N5_6_TREATMENT_TYPE_4 LIKE '0[12356]'
                       THEN mcp.N5_6_TREATMENT_TYPE_4  + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_4 = '14'
                       THEN '02' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_4 = '13'
                       THEN '03' + ';'
                       WHEN mcp.N5_6_TREATMENT_TYPE_4 = '11'
                       THEN '05' + ';'
                       ELSE ''
                   END)),'')
				ELSE NULL
				END AS PlannedCancerTreatmentType,
			CASE
					WHEN mcp.N5_5_CARE_INTENT = 'X' AND mcp.N5_8_NO_TREATMENT = '01'
					THEN mcp.N5_8_NO_TREATMENT
					WHEN mcp.N5_5_CARE_INTENT = 'X' AND mcp.N5_8_NO_TREATMENT LIKE '0[236]'
					THEN '03'
					WHEN mcp.N5_5_CARE_INTENT = 'X' AND mcp.N5_8_NO_TREATMENT IN ('04', '10')
					THEN '04'
					WHEN mcp.N5_5_CARE_INTENT = 'X' AND 
						(mcp.N5_8_NO_TREATMENT LIKE '0[578]' OR mcp.N5_8_NO_TREATMENT = '99')
					THEN '99'					
					--WHEN mcp.N5_5_CARE_INTENT = '9' AND mcp.R_NOT_CUR = '01'
					--THEN mcp.R_NOT_CUR
					--WHEN mcp.N5_5_CARE_INTENT = '9' AND mcp.R_NOT_CUR LIKE '0[236]'
					--THEN '03'
					--WHEN mcp.N5_5_CARE_INTENT = '9' AND mcp.R_NOT_CUR IN ('04', '10')
					--THEN '04'
					--WHEN mcp.N5_5_CARE_INTENT = '9' AND 
					--	(mcp.R_NOT_CUR LIKE '0[578]' OR mcp.R_NOT_CUR = '99')
					--THEN '99'					
					ELSE NULL									
			END NoCancerTreatmentReason,					
			CASE
			WHEN 'T' + LOWER(nbt.ClinicalTStage) = 'Tx' 
			THEN 'T' + LOWER(nbt.ClinicalTStage)
			WHEN 'T' + nbt.ClinicalTStage LIKE 'T[1239]'
			THEN 'T' + nbt.ClinicalTStage
			WHEN 'T' + nbt.ClinicalTStage = 'T4' AND (nbt.ClinicalTLetter NOT IN ('a','b') OR nbt.ClinicalTLetter IS NULL)
			THEN 'T' + nbt.ClinicalTStage
			WHEN 'T' + nbt.ClinicalTStage + nbt.ClinicalTLetter LIKE 'T4[ab]'
			THEN 'T' + nbt.ClinicalTStage + nbt.ClinicalTLetter
			ELSE 'T9'
			END TCategory,
			CASE
			WHEN 'N' + LOWER(nbt.ClinicalNStage) = 'Nx'
			THEN 'N' + LOWER(nbt.ClinicalNStage)
			WHEN 'N' + nbt.ClinicalNStage LIKE 'N[0129]'
			THEN 'N' + nbt.ClinicalNStage
			ELSE 'N9'
			END NCategory,			
			CASE
			WHEN 'M' + LOWER(nbt.ClinicalMStage) = 'Mx' 
			THEN 'M' + LOWER(nbt.ClinicalMStage)
			WHEN 'M' + nbt.ClinicalMStage LIKE 'M[019]'
			THEN 'M' + nbt.ClinicalMStage
			ELSE 'M9'
			END MCategory,
			CASE 
			WHEN nbam.EVENT_ID IS NOT NULL AND m.L_MONITORING_INTENT IN ('1','2','3')
			THEN m.L_MONITORING_INTENT
			WHEN nbam.EVENT_ID IS NOT NULL AND m.L_MONITORING_INTENT IS NULL
			THEN NULL			
			ELSE '4'
			END MonitoringIntent			
	FROM	vwNBOCAP_BASE_TUMOURS nbt
			LEFT JOIN tblREFERRAL_COLORECTAL rc
			ON nbt.CARE_ID = rc.CARE_ID
			--LEFT JOIN vwNBOCAP_CARE_PLANS mcp			
			--ON nbt.CARE_ID = mcp.CARE_ID
			--
			LEFT JOIN vwNBOCAP_QUALIFYING_CARE_PLANS nqcp
			ON nbt.CARE_ID = nqcp.CARE_ID
			LEFT JOIN tblMAIN_CARE_PLAN mcp
			ON nqcp.CARE_ID = mcp.CARE_ID AND
			nqcp.EVENT_ID = mcp.PLAN_ID
			--
			LEFT JOIN vwNBOCAP_BASE_ACTIVE_MONITORING nbam
			ON nbt.CARE_ID = nbam.CARE_ID						
			LEFT JOIN tblMONITORING m
			ON nbam.EVENT_ID = m.MONITOR_ID
			LEFT JOIN tblDEMOGRAPHICS d
			ON nbt.PATIENT_ID = d.PATIENT_ID
	WHERE	(dbo.IsNSTSValid(d.PATIENT_ID)) = 1
			AND d.N1_10_DATE_BIRTH IS NOT NULL
			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