CWTBasicData

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CareID

int

null

NHSNumber

varchar

null

NHSNumberStatusIndicator

varchar

√

null

PatientPathwayID

varchar

√

null

PatientPathwayIDIssuer

varchar

√

null

PriorityTypeCode

varchar

√

null

CancerReferralToTreatmentStartDate

smalldatetime

√

null

Forename

varchar

√

null

Surname

varchar

√

null

HospitalNumber

varchar

√

null

CancerSite

varchar

√

null

IsTemporaryNHSNumber

int

null

PatientStatus

varchar

√

null

SourceOfReferral

varchar

√

null

ReferralType

varchar

√

null

RawDiagnosis

varchar

√

null

PrimaryDiagnosisICD

varchar

√

null

SNOMedCT

bigint

√

null

DateOfBirth

date

√

null

DiagnosisDate

smalldatetime

√

null

DateFirstSeen

smalldatetime

√

null

OrgFirstSeen

varchar

√

null

UpgradeDate

smalldatetime

√

null

UpgradeOrganisation

varchar

√

null

InvalidDiagnosis

int

null

FirstSeenWaitingTimeAdjustment

int

√

null

FirstSeenWaitingTimeAdjustmentReason

int

√

null

PathwayStartDate

smalldatetime

√

null

Consultant

varchar

√

null

TumourStatus

int

null

View Definition

CREATE VIEW [dbo].[CWTBasicData]
AS
WITH ReferralCWTDiagnosis AS (
	SELECT
		CARE_ID AS CareID,
		CASE
			WHEN mr.N4_2_DIAGNOSIS_CODE = 'C339A'
			THEN 'C33X'
			WHEN ((LEFT(mr.L_SEC_DIAGNOSIS,1) = 'C') AND (RTRIM(mr.L_SEC_DIAGNOSIS) <> 'C80X') AND (LEFT(mr.N4_2_DIAGNOSIS_CODE,3) LIKE 'C7[7-9]'))
			THEN 
				CASE
					WHEN (LEN(RTRIM(mr.L_SEC_DIAGNOSIS)) = 4) AND RIGHT(mr.L_SEC_DIAGNOSIS, 1) = 'X'
					THEN mr.L_SEC_DIAGNOSIS
					WHEN (LEN(RTRIM(mr.L_SEC_DIAGNOSIS)) = 4)
					THEN LEFT(mr.L_SEC_DIAGNOSIS,3)+'.'+SUBSTRING(mr.L_SEC_DIAGNOSIS,4,1) 
					ELSE LEFT(mr.L_SEC_DIAGNOSIS,3)+'.9'
				END
			WHEN (LEN(mr.N4_2_DIAGNOSIS_CODE) <= 3)
			THEN NULL
			WHEN (LEN(RTRIM(mr.N4_2_DIAGNOSIS_CODE)) = 4) AND RIGHT(mr.N4_2_DIAGNOSIS_CODE, 1) = 'X'
			THEN mr.N4_2_DIAGNOSIS_CODE
			ELSE LEFT(mr.N4_2_DIAGNOSIS_CODE,3)+'.'+SUBSTRING(mr.N4_2_DIAGNOSIS_CODE,4,1)	
		END
	AS PrimaryDiagnosisICD,
	sn.CT_Concept_ID as SNOMedCT
	FROM tblMAIN_REFERRALS mr
	LEFT JOIN ltblSNOMedCT sn ON
		mr.SNOMed_CT = sn.CT_Snomed_ID
), NullSafeData AS (
	SELECT
		CareID,
		PrimaryDiagnosisICD,
		ISNULL(PrimaryDiagnosisICD, '') AS NullSafeDiagnosis,
		SNOMedCT,
		ISNULL(SNOMedCT, '') AS NullSafeSnomed
	FROM
		ReferralCWTDiagnosis
), FilteredCWTDiagnosis AS (
	SELECT
		CareID,
		PrimaryDiagnosisICD,
		SNOMedCT,
		CASE
			WHEN 
			(NullSafeDiagnosis LIKE 'C44%' AND NullSafeSnomed in (1338007,61098004,134152008,43369006,37304002,6641007,24762001))
			OR (LEFT(NullSafeDiagnosis,1) = 'D' AND LEFT(NullSafeDiagnosis,3) <> 'D05')
			OR (NullSafeDiagnosis LIKE 'E85%')
			THEN 1
			ELSE 0
		END AS InvalidDiagnosis
	FROM
		NullSafeData
)
SELECT
mr.CARE_ID AS CareID,
d.N1_1_NHS_NUMBER AS NHSNumber,
d.NHS_NUMBER_STATUS AS NHSNumberStatusIndicator,
dt.PATHWAY_ID AS PatientPathwayID,
CASE
	WHEN mr.L_REFERRAL_METHOD = 7
	THEN 'X09'
	ELSE LEFT(mr.N1_3_ORG_CODE_SEEN, 3)
END AS PatientPathwayIDIssuer,
mr.N2_4_PRIORITY_TYPE AS PriorityTypeCode,
mr.N2_6_RECEIPT_DATE AS CancerReferralToTreatmentStartDate,
d.N1_6_FORENAME AS Forename,
d.N1_5_SURNAME AS Surname,
d.N1_2_HOSPITAL_NUMBER AS HospitalNumber,
mr.L_CANCER_SITE AS CancerSite,
CASE 
	WHEN d.L_NSTS_STATUS IN(0,9) THEN 1
	ELSE 0
END AS IsTemporaryNHSNumber,
CASE 
	WHEN mr.N2_13_CANCER_STATUS = '69' THEN '03' 
	ELSE RIGHT('0' + mr.N2_13_CANCER_STATUS,2) 
END AS PatientStatus,
CASE
	WHEN ISNULL(mr.N2_16_OP_REFERRAL, '') IN ('20', '21', '99') THEN '97'
	ELSE mr.N2_16_OP_REFERRAL
END AS SourceOfReferral,
CASE
	WHEN (mr.N2_4_PRIORITY_TYPE IN('02','03')) THEN mr.N2_12_CANCER_TYPE
	ELSE NULL
END AS ReferralType,
mr.N4_2_DIAGNOSIS_CODE AS RawDiagnosis,
fcd.PrimaryDiagnosisICD,
fcd.SNOMedCT,
d.N1_10_DATE_BIRTH AS DateOfBirth,
mr.N4_1_DIAGNOSIS_DATE AS DiagnosisDate,
mr.N2_9_FIRST_SEEN_DATE as DateFirstSeen,
mr.N1_3_ORG_CODE_SEEN AS OrgFirstSeen,
mr.N_UPGRADE_DATE as UpgradeDate,
mr.N_UPGRADE_ORG_CODE AS UpgradeOrganisation,
fcd.InvalidDiagnosis,
CASE
	WHEN mr.N2_15_ADJ_REASON = 3 AND mr.L_CANCELLED_DATE IS NOT NULL
		THEN DATEDIFF(d, mr.N2_6_RECEIPT_DATE, mr.L_CANCELLED_DATE)
	ELSE 0
END AS FirstSeenWaitingTimeAdjustment,
CASE
	WHEN mr.N2_15_ADJ_REASON = 3 AND mr.L_CANCELLED_DATE IS NOT NULL THEN mr.N2_15_ADJ_REASON
END AS FirstSeenWaitingTimeAdjustmentReason,
CASE
	WHEN mr.L_CANCELLED_DATE IS NOT NULL AND mr.N2_6_RECEIPT_DATE IS NOT NULL AND
		 mr.L_CANCELLED_DATE >= mr.N2_6_RECEIPT_DATE AND mr.N2_15_ADJ_REASON = 3
	THEN mr.L_CANCELLED_DATE
	WHEN mr.L_CANCELLED_DATE IS NOT NULL AND mr.N_UPGRADE_DATE IS NOT NULL AND
		 mr.L_CANCELLED_DATE >= mr.N_UPGRADE_DATE AND mr.N2_15_ADJ_REASON = 3
	THEN mr.L_CANCELLED_DATE	
	WHEN mr.N_UPGRADE_DATE IS NOT NULL AND mr.N2_6_RECEIPT_DATE IS NOT NULL AND
		 mr.N_UPGRADE_DATE >= mr.N2_6_RECEIPT_DATE
	THEN mr.N_UPGRADE_DATE	
	WHEN mr.N_UPGRADE_DATE IS NOT NULL AND mr.L_CANCELLED_DATE IS NOT NULL AND
		 mr.N_UPGRADE_DATE >= mr.L_CANCELLED_DATE
	THEN mr.N_UPGRADE_DATE	
	WHEN mr.N2_6_RECEIPT_DATE IS NOT NULL
	THEN mr.N2_6_RECEIPT_DATE	
	ELSE NULL
END AS PathwayStartDate,
CASE
	WHEN cbc.CON_CODE IS NULL
	THEN RTRIM(cbc.CON_DESC)
	ELSE RTRIM(cbc.CON_DESC) + ' - ' + cbc.CON_CODE
END AS Consultant,
mr.L_TUMOUR_STATUS AS TumourStatus

FROM tblMAIN_REFERRALS mr
INNER JOIN FilteredCWTDiagnosis fcd ON fcd.CareID = mr.CARE_ID
INNER JOIN tblDEFINITIVE_TREATMENT dt ON dt.CARE_ID = mr.CARE_ID AND dt.TREAT_NO = 1
INNER JOIN tblDEMOGRAPHICS d ON mr.PATIENT_ID = d.PATIENT_ID
LEFT JOIN ConsultantsByCode cbc
ON mr.L_FIRST_CONSULTANT = cbc.NATIONAL_CODE
WHERE 
	(
		mr.L_INAP_REF <> 1
		OR mr.L_INAP_REF IS NULL
	)
	AND mr.TRANSFER_REASON IS NULL

Columns

View Definition

Possibly Referenced Tables/Views