vwNBOCAP_BASE_EVENTS

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

CHEMORADIOTHERAPY

varchar

null

View Definition

-- Created by Paul Stevens, February 2014
-- Amended by Paul Stevens, April 2014

CREATE VIEW [dbo].[vwNBOCAP_BASE_EVENTS]
AS

SELECT CARE_ID, EVENT_ID, EVENT_DATE, EVENT_TYPE, CHEMORADIOTHERAPY FROM
(SELECT 
CARE_ID AS CARE_ID
,PLAN_ID AS EVENT_ID
,CASE
WHEN N5_2_MDT_DATE IS NOT NULL
THEN N5_2_MDT_DATE 
WHEN N5_3_PLAN_AGREE_DATE IS NOT NULL
THEN N5_3_PLAN_AGREE_DATE
END EVENT_DATE
,'CARE_PLAN' AS EVENT_TYPE
,'0' AS CHEMORADIOTHERAPY
FROM tblMAIN_CARE_PLAN
UNION ALL
SELECT 
CARE_ID AS CARE_ID
,SURGERY_ID AS EVENT_ID
,N7_9_SURGERY_DATE AS EVENT_DATE
,'SURGERY' AS EVENT_TYPE
,'0' AS CHEMORADIOTHERAPY
FROM tblMAIN_SURGERY
WHERE 
SURGERY_ID IN (SELECT EVENT_ID FROM vwNBOCAP_BASE_SURGERIES)
UNION ALL
SELECT
CARE_ID AS CARE_ID
,CHEMO_ID AS EVENT_ID
,N9_10_START_DATE AS EVENT_DATE
,'CHEMO' AS EVENT_TYPE
,'1' AS CHEMORADIOTHERAPY
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
,'1' AS CHEMORADIOTHERAPY
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
,'1' AS CHEMORADIOTHERAPY
FROM tblMAIN_BRACHYTHERAPY
UNION ALL
SELECT
CARE_ID AS CARE_ID
,PATHOLOGY_ID AS EVENT_ID
,N8_3_RESULT_DATE AS EVENT_DATE
,'PATHOLOGY' AS EVENT_TYPE
,'0' AS CHEMORADIOTHERAPY
FROM tblMAIN_PATHOLOGY
WHERE 
(N8_1_PATHOLOGY_TYPE LIKE '[EFPR][E]' OR N8_1_PATHOLOGY_TYPE = 'EX') AND
N8_22_SPECIMEN_NATURE = '1' AND
N8_21_REPORT_STATUS = '1'
UNION ALL
SELECT
CARE_ID AS CARE_ID
,MONITOR_ID AS EVENT_ID
,N16_10_START_ACTIVE AS EVENT_DATE
,'MONITOR' AS EVENT_TYPE
,'0' AS CHEMORADIOTHERAPY
FROM tblMONITORING) t
WHERE CARE_ID IN (SELECT CARE_ID FROM vwNBOCAP_BASE_TUMOURS)

Columns

View Definition

Possibly Referenced Tables/Views