GET_TREATMENTS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

TreatmentType

int

null

CareID

int

null

TreatmentID

int

null

StartDate

datetime

16,3

√

null

View Definition

CREATE VIEW [dbo].[GET_TREATMENTS]
--Created by Paul Thornton, Oct '16
AS
	--Surgery
	SELECT
		1 AS TreatmentType,
		su.CARE_ID as CareID,
		su.SURGERY_ID AS TreatmentID,
		su.N7_9_SURGERY_DATE AS StartDate
	FROM tblMAIN_SURGERY su
	UNION
    --Anti-cancer Drugs
	SELECT
		2 AS TreatmentType,
		mc.CARE_ID as CareID,
		mc.CHEMO_ID AS TreatmentID,
		mc.N9_10_START_DATE AS StartDate
	FROM tblMAIN_CHEMOTHERAPY mc
	UNION
	--Teletherapy
	SELECT
		3 AS TreatmentType,
		mt.CARE_ID as CareID,
		mt.TELE_ID AS TreatmentID,
		mt.N10_8_START_DATE AS StartDate
	FROM tblMAIN_TELETHERAPY mt
	LEFT OUTER JOIN tblDEFINITIVE_TREATMENT dt
		ON dt.CARE_ID = mt.CARE_ID AND dt.TREAT_ID = mt.TELE_ID AND
			ISNULL(dt.TREATMENT, '') IN ('05', '13', '22')
	UNION
	--Brachytherapy
	SELECT
		4 AS TreatmentType,
		mb.CARE_ID as CareID,
		mb.BRACHY_ID AS TreatmentID,
		mb.N11_9_START_DATE AS StartDate
	FROM tblMAIN_BRACHYTHERAPY mb
	UNION
	--Palliative
	SELECT
		5 AS TreatmentType,
		mp.CARE_ID as CareID,
		mp.PALL_ID AS TreatmentID,
		mp.N12_2_START_DATE AS StartDate
	FROM tblMAIN_PALLIATIVE mp
	UNION
	--Active Monitoring
	SELECT
		6 AS TreatmentType,
		mon.CARE_ID as CareID,
		mon.MONITOR_ID AS TreatmentID,
		mon.N16_10_START_ACTIVE AS StartDate
	FROM tblMONITORING mon
	UNION
	--Other
	SELECT
		7 AS TreatmentType,
		ot.CARE_ID as CareID,
		ot.OTHER_ID AS TreatmentID,
		ot.N16_10_START_ACTIVE AS StartDate
	FROM tblOTHER_TREATMENT ot

Columns

View Definition

Possibly Referenced Tables/Views