BIvwAllTreatments

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

NHS Number

varchar

null

Hospital Number

varchar

√

null

Full Name

varchar

111

√

null

Treatment Type

varchar

null

Treatment Event Type

varchar

100

√

null

Treatment Intent

varchar

√

null

Adjunctive Therapy

varchar

√

null

Treatment Setting

varchar

√

null

Consultant

varchar

√

null

Decision to treat date

datetime

16,3

√

null

Treatment start date

datetime

16,3

√

null

Organisation (Treatment) Site Code

varchar

√

null

Organisation (Treatment) Name

nvarchar

500

√

null

Discharge date

smalldatetime

√

null

Waiting Time Adj DTT

int

√

null

Validated for upload

varchar

null

View Definition

CREATE VIEW [dbo].[BIvwAllTreatments] AS
WITH AllTreatments AS (SELECT
	SURGERY_ID AS TREAT_ID,
	tblMAIN_SURGERY.CARE_ID,
	'Surgery' AS TreatType,
	N_TREATMENT_EVENT AS TreatEvent,
	ti.SurgicalIntentDesc AS TreatIntent,
	ltblAdjunctiveTherapy.AdjunctiveTherapyDesc as [Adjunctive Therapy],
	N_TREATMENT_SETTING AS TreatSetting,
	N7_1_SITE_CODE AS [Organisation (Treatment) Site Code],
	Description AS [Organisation (Treatment) Name],
	N7_2_CONSULTANT AS ConCode,
	N7_5_DECISION_DATE AS DecisionDate,
	N7_8_ADMISSION_DATE AS StartDate,
	N7_12_DISCHARGE_DATE AS DischargeDate,
	tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
	VALIDATED
FROM tblMAIN_SURGERY
LEFT JOIN ltblTreatmentIntent ti ON
	N7_4_TREATMENT_INTENT = ti.IntentID
LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblMAIN_SURGERY.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblMAIN_SURGERY.SURGERY_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT IN('23','24', '10', '11', '12', '16', '17', '20', '23', '24')
LEFT JOIN OrganisationSites ON 
	tblMAIN_SURGERY.N7_1_SITE_CODE = OrganisationSites.Code
	LEFT JOIN ltblAdjunctiveTherapy ON tblMAIN_SURGERY.AdjunctiveTherapyID = ltblAdjunctiveTherapy.AdjunctiveTherapyID
	
UNION ALL

SELECT
	TREAT_ID,
	CARE_ID,
	TreatType,
	TreatEvent,
	TreatIntentDesc AS TreatIntent,
	[Adjunctive Therapy],
	TreatSetting,
	[Organisation (Treatment) Site Code],
	[Organisation (Treatment) Name],
	ConCode,
	DecisionDate,
	StartDate,
	DischargeDate,
	[Waiting Time Adj DTT],
	VALIDATED
FROM (SELECT
		CHEMO_ID AS TREAT_ID,
		tblMAIN_CHEMOTHERAPY.CARE_ID,
		'Chemotherapy' AS TreatType,
		N_TREATMENT_EVENT AS TreatEvent,
		ti.NonSurgicalIntentDesc AS TreatIntentDesc,
		ltblAdjunctiveTherapy.AdjunctiveTherapyDesc as [Adjunctive Therapy],
		N_TREATMENT_SETTING AS TreatSetting,
		N9_1_SITE_CODE AS [Organisation (Treatment) Site Code],
		Description AS [Organisation (Treatment) Name],
		N9_2_CONSULTANT AS ConCode,
		N9_4_DECISION_DATE AS DecisionDate,
		N9_10_START_DATE AS StartDate,
		NULL AS DischargeDate,
		tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
		VALIDATED
	FROM tblMAIN_CHEMOTHERAPY
	LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblMAIN_CHEMOTHERAPY.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblMAIN_CHEMOTHERAPY.CHEMO_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT IN('02', '03', '14', '15', '21')
	LEFT JOIN OrganisationSites ON 
	tblMAIN_CHEMOTHERAPY.N9_1_SITE_CODE = OrganisationSites.Code	
	LEFT JOIN ltblTreatmentIntent ti ON N9_8_TREATMENT_INTENT = ti.IntentID
	LEFT JOIN ltblAdjunctiveTherapy ON tblMAIN_CHEMOTHERAPY.AdjunctiveTherapyID = ltblAdjunctiveTherapy.AdjunctiveTherapyID
UNION ALL
	SELECT
		TELE_ID AS TREAT_ID,
		tblMAIN_TELETHERAPY.CARE_ID,
		'Teletherapy' AS TreatType,
		N_TREATMENT_EVENT AS TreatEvent,
		ti.NonSurgicalIntentDesc AS TreatIntentDesc,
		ltblAdjunctiveTherapy.AdjunctiveTherapyDesc as [Adjunctive Therapy],
		N_TREATMENT_SETTING AS TreatSetting,
		N10_1_SITE_CODE AS [Organisation (Treatment) Site Code],
		Description AS [Organisation (Treatment) Name],
		N10_2_CONSULTANT AS ConCode,
		N10_3_DECISION_DATE AS DecisionDate,
		N10_8_START_DATE AS StartDate,
		NULL AS DischargeDate,
		tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
		VALIDATED
	FROM tblMAIN_TELETHERAPY
	LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblMAIN_TELETHERAPY.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblMAIN_TELETHERAPY.TELE_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT IN('05', '13', '22')
	LEFT JOIN OrganisationSites ON 
	tblMAIN_TELETHERAPY.N10_1_SITE_CODE = OrganisationSites.Code
	LEFT JOIN ltblTreatmentIntent ti ON N10_6_TREATMENT_INTENT = ti.IntentID
	LEFT JOIN ltblAdjunctiveTherapy ON tblMAIN_TELETHERAPY.AdjunctiveTherapyID = ltblAdjunctiveTherapy.AdjunctiveTherapyID
	UNION ALL
	SELECT
		BRACHY_ID AS TREAT_ID,
		tblMAIN_BRACHYTHERAPY.CARE_ID,
		'Brachytherapy' AS TreatType,
		N_TREATMENT_EVENT AS TreatEvent,
		ti.NonSurgicalIntentDesc AS TreatIntentDesc,
		ltblAdjunctiveTherapy.AdjunctiveTherapyDesc as [Adjunctive Therapy],
		N_TREATMENT_SETTING AS TreatSetting,
		N11_1_SITE_CODE AS [Organisation (Treatment) Site Code],
		Description AS [Organisation (Treatment) Name],
		N11_2_CONSULTANT AS ConCode,
		N11_3_DECISION_DATE AS DecisionDate,
		N11_9_START_DATE AS StartDate,
		NULL AS DischargeDate,
		tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
		VALIDATED
	FROM tblMAIN_BRACHYTHERAPY
	LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblMAIN_BRACHYTHERAPY.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblMAIN_BRACHYTHERAPY.BRACHY_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT IN('06', '19')
	LEFT JOIN OrganisationSites ON 
	tblMAIN_BRACHYTHERAPY.N11_1_SITE_CODE = OrganisationSites.Code
	LEFT JOIN ltblTreatmentIntent ti ON N11_6_TREATMENT_INTENT = ti.IntentID
	LEFT JOIN ltblAdjunctiveTherapy ON tblMAIN_BRACHYTHERAPY.AdjunctiveTherapyID = ltblAdjunctiveTherapy.AdjunctiveTherapyID) ChemoTeleBrachy
	
UNION ALL
SELECT
	PALL_ID AS TREAT_ID,
	tblMAIN_PALLIATIVE.CARE_ID,
	'Palliative Care' AS TreatType,
	N_TREATMENT_EVENT AS TreatEvent,
	'Palliative' AS TreatIntent,
	NULL as [Adjunctive Therapy],
	N_TREATMENT_SETTING AS TreatSetting,
	N1_3_ORG_CODE_TREATMENT AS [Organisation (Treatment) Site Code],
	Description AS [Organisation (Treatment) Name],
	N7_2_CONSULTANT AS ConCode,
	N12_1_DECISION_DATE AS DecisionDate,
	N12_2_START_DATE AS StartDate,
	NULL AS DischargeDate,
	tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
	VALIDATED
FROM tblMAIN_PALLIATIVE
	LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblMAIN_PALLIATIVE.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblMAIN_PALLIATIVE.PALL_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT IN('07', '09')
	LEFT JOIN OrganisationSites ON 
	tblMAIN_PALLIATIVE.N1_3_ORG_CODE_TREATMENT = OrganisationSites.Code
UNION ALL
SELECT
	MONITOR_ID AS TREAT_ID,
	tblMONITORING.CARE_ID,
	'Active Monitoring' AS TreatType,
	N_TREATMENT_EVENT AS TreatEvent,
	NATIONAL_DESC AS TreatIntent,
	NULL as [Adjunctive Therapy],
	N_TREATMENT_SETTING AS TreatSetting,
	N1_3_ORG_CODE_TREATMENT AS [Organisation (Treatment) Site Code],
	Description AS [Organisation (Treatment) Name],
	N7_2_CONSULTANT AS ConCode,
	N16_9_DECISION_ACTIVE AS DecisionDate,
	N16_10_START_ACTIVE AS StartDate,
	NULL AS DischargeDate,
	tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
	VALIDATED
FROM tblMONITORING
LEFT JOIN ltblMONITORING_INTENT ON
	ltblMONITORING_INTENT.NATIONAL_CODE = L_MONITORING_INTENT
LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblMONITORING.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblMONITORING.MONITOR_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT = '08'
LEFT JOIN OrganisationSites ON 
	tblMONITORING.N1_3_ORG_CODE_TREATMENT = OrganisationSites.Code
UNION ALL
SELECT
	OTHER_ID AS TREAT_ID,
	tblOTHER_TREATMENT.CARE_ID,
	'Other' AS TreatType,
	N_TREATMENT_EVENT AS TreatEvent,
	NULL AS TreatIntent,
	ltblAdjunctiveTherapy.AdjunctiveTherapyDesc as [Adjunctive Therapy],
	N_TREATMENT_SETTING AS TreatSetting,
	N1_3_ORG_CODE_TREATMENT AS [Organisation (Treatment) Site Code],
	Description AS [Organisation (Treatment) Name],
	N7_2_CONSULTANT AS ConCode,
	N16_9_DECISION_ACTIVE AS DecisionDate,
	N16_10_START_ACTIVE AS StartDate,
	NULL AS DischargeDate,
	tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
	VALIDATED
FROM tblOTHER_TREATMENT
LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblOTHER_TREATMENT.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblOTHER_TREATMENT.OTHER_ID
	AND tblDEFINITIVE_TREATMENT.TREATMENT = '97'
	LEFT JOIN OrganisationSites ON 
	tblOTHER_TREATMENT.N1_3_ORG_CODE_TREATMENT = OrganisationSites.Code
	LEFT JOIN ltblAdjunctiveTherapy ON tblOTHER_TREATMENT.AdjunctiveTherapyID = ltblAdjunctiveTherapy.AdjunctiveTherapyID
	
UNION ALL
SELECT
	AllTreatmentDeclinedID AS TREAT_ID,
	tblAllTreatmentDeclined.CARE_ID,
	'All Treatment Declined' AS TreatType,
	N_TREATMENT_EVENT AS TreatEvent,
	NULL AS TreatIntent,
	NULL as [Adjunctive Therapy],
	N_TREATMENT_SETTING AS TreatSetting,
	ORG_CODE_TREATMENT AS [Organisation (Treatment) Site Code],
	Description AS [Organisation (Treatment) Name],
	NULL AS ConCode,
	tblAllTreatmentDeclined.DECISION_DATE AS DecisionDate,
	tblAllTreatmentDeclined.START_DATE AS StartDate,
	NULL AS DischargeDate,
	tblDEFINITIVE_TREATMENT.ADJ_DAYS as [Waiting Time Adj DTT],
	VALIDATED
FROM tblAllTreatmentDeclined
LEFT JOIN tblDEFINITIVE_TREATMENT ON
	tblDEFINITIVE_TREATMENT.CARE_ID = tblAllTreatmentDeclined.CARE_ID
	AND tblDEFINITIVE_TREATMENT.TREAT_ID = tblAllTreatmentDeclined.AllTreatmentDeclinedID
	AND tblDEFINITIVE_TREATMENT.TREATMENT = '98'
	LEFT JOIN OrganisationSites ON 
	tblAllTreatmentDeclined.ORG_CODE_TREATMENT = OrganisationSites.Code)

SELECT
	tblMAIN_REFERRALS.CARE_ID,
	tblDEMOGRAPHICS.N1_1_NHS_NUMBER AS 'NHS Number',
	tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER AS 'Hospital Number',
	tblDEMOGRAPHICS.N1_6_FORENAME + ' ' + tblDEMOGRAPHICS.N1_5_SURNAME AS 'Full Name',
	AllTreatments.TreatType AS 'Treatment Type',
	ltblTREATMENT_EVENT.EVENT_DESC AS 'Treatment Event Type',
	AllTreatments.TreatIntent AS 'Treatment Intent',
	AllTreatments.[Adjunctive Therapy] as 'Adjunctive Therapy',
	ltblTREATMENT_SETTING.SET_DESC AS 'Treatment Setting',
	AllTreatments.ConCode As Consultant,
	AllTreatments.DecisionDate As 'Decision to treat date',
	AllTreatments.StartDate As 'Treatment start date',
	AllTreatments.[Organisation (Treatment) Site Code] AS 'Organisation (Treatment) Site Code',
	AllTreatments.[Organisation (Treatment) Name] AS 'Organisation (Treatment) Name',
	AllTreatments.DischargeDate As 'Discharge date',
	AllTreatments.[Waiting Time Adj DTT],
	CASE
		WHEN ISNULL(AllTreatments.VALIDATED, '') = 1 THEN 'Yes'
		ELSE ''
	END AS 'Validated for upload'
FROM tblMAIN_REFERRALS
INNER JOIN tblDEMOGRAPHICS ON
	tblDEMOGRAPHICS.PATIENT_ID = tblMAIN_REFERRALS.PATIENT_ID
INNER JOIN AllTreatments ON
	AllTreatments.CARE_ID = tblMAIN_REFERRALS.CARE_ID
LEFT JOIN ltblTREATMENT_EVENT ON
	ltblTREATMENT_EVENT.EVENT_CODE = AllTreatments.TreatEvent
LEFT JOIN ltblTREATMENT_SETTING ON
	ltblTREATMENT_SETTING.SET_CODE = AllTreatments.TreatSetting

Columns

View Definition

Possibly Referenced Tables/Views