vwNBOCAP_BASE_EVENTS_CARE_PLANS_CHEMOS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

EVENT_ID

int

null

EVENT_DATE

smalldatetime

√

null

EVENT_TYPE

varchar

null

View Definition

-- Created by Paul Stevens, May 2014
-- Returns care plans, chemos and surgeries for all qualifying NBOCAP referrals

--If there is a care plan date then use that date to determine if the record is
--the last before the most appropriate treatment or the soonest after the treatment.
--If there is no care plan date, but there is an MDT date and the ‘was care plan agreed at MDT’ field 
--is set to ‘Yes’ then use the MDT date to determine if the record is the last before 
--the most appropriate treatment or the soonest after the treatment.
--If both of the following cases are true then the Care Plan/MDT record will be ignored;
--a.	There is no Care Plan Date
--b.	The MDT date is not valid (i.e. blank) or the ‘was care plan agreed at MDT’ field is not ‘Yes’.

CREATE VIEW [dbo].[vwNBOCAP_BASE_EVENTS_CARE_PLANS_CHEMOS]
AS

SELECT t.CARE_ID, EVENT_ID, EVENT_DATE, EVENT_TYPE FROM
(SELECT 
CARE_ID AS CARE_ID
,PLAN_ID AS EVENT_ID
,CASE
WHEN ISDATE(N5_3_PLAN_AGREE_DATE) = 1
THEN N5_3_PLAN_AGREE_DATE
WHEN ISDATE(N5_2_MDT_DATE) = 1 AND L_CARE_PLAN_AGREED = 'Y'
THEN N5_2_MDT_DATE
END EVENT_DATE
,'CARE_PLAN' AS EVENT_TYPE
FROM tblMAIN_CARE_PLAN
UNION ALL
--SELECT
--CARE_ID AS CARE_ID
--,CHEMO_ID AS EVENT_ID
--,N9_10_START_DATE AS EVENT_DATE
--,'CHEMO' AS EVENT_TYPE
--FROM tblMAIN_CHEMOTHERAPY
--UNION ALL
--SELECT
--CARE_ID AS CARE_ID
--,TELE_ID AS EVENT_ID
--,N10_8_START_DATE AS EVENT_DATE
--,'TELE' AS EVENT_TYPE
--FROM tblMAIN_TELETHERAPY
--UNION ALL
--SELECT
--CARE_ID AS CARE_ID
--,BRACHY_ID AS EVENT_ID
--,N11_9_START_DATE AS EVENT_DATE
--,'BRACHY' AS EVENT_TYPE
--FROM tblMAIN_BRACHYTHERAPY
--UNION ALL
SELECT 
CARE_ID AS CARE_ID
,SURGERY_ID AS EVENT_ID
,N7_9_SURGERY_DATE AS EVENT_DATE
,'SURGERY' AS EVENT_TYPE
FROM tblMAIN_SURGERY
WHERE 
SURGERY_ID IN (SELECT EVENT_ID FROM vwNBOCAP_BASE_SURGERIES)) t
LEFT JOIN tblMAIN_REFERRALS mr
ON t.CARE_ID = mr.CARE_ID
LEFT JOIN tblDEMOGRAPHICS d 
ON mr.PATIENT_ID = d.PATIENT_ID
WHERE mr.L_CANCER_SITE = 'Colorectal' AND	
(mr.N4_2_DIAGNOSIS_CODE LIKE 'C18[0-7]' OR SUBSTRING(mr.N4_2_DIAGNOSIS_CODE,1,3) IN ('C19','C20')) AND
((dbo.getAgeAtDiagnosis(mr.CARE_ID) >= 18 AND YEAR(d.N1_10_DATE_BIRTH) > 1900) OR d.N1_10_DATE_BIRTH IS NULL) AND
ISNULL(L_TUMOUR_STATUS, 0) <> 4
AND EVENT_DATE IS NOT NULL

Columns

View Definition

Possibly Referenced Tables/Views