vwNBOCAP_BASE_SURGERIES

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

NBOCAP_ORDER

int

√

null

EVENT_ID

int

√

null

View Definition

-- Created by Paul Stevens, February 2014

CREATE VIEW [dbo].[vwNBOCAP_BASE_SURGERIES]
AS

SELECT t.CARE_ID, t.NBOCAP_ORDER, MIN(t.SURGERY_ID) AS EVENT_ID
FROM 
(
SELECT ms.CARE_ID, ms.SURGERY_ID, 
CASE
WHEN nps.NBOCAP_ORDER IS NULL
THEN 21
ELSE nps.NBOCAP_ORDER
END NBOCAP_ORDER
FROM tblMAIN_SURGERY ms LEFT JOIN tblMAIN_REFERRALS mr
ON ms.CARE_ID = mr.CARE_ID
LEFT JOIN ltblNBOCAP_PREFERRED_SURGERY nps
ON ms.L_PROCEDURE = nps.PROC_ID AND
mr.N4_2_DIAGNOSIS_CODE = nps.NBOCAP_DIAGNOSIS
WHERE ms.CARE_ID IN (SELECT CARE_ID FROM vwNBOCAP_BASE_TUMOURS)
AND (ms.L_PROCEDURE IS NOT NULL AND ms.L_PROCEDURE <> '0') AND
ISDATE(ms.N7_9_SURGERY_DATE) = 1) t
JOIN
(
SELECT u.CARE_ID, MIN(u.NBOCAP_ORDER) AS NBOCAP_ORDER
FROM 
(
SELECT ms.CARE_ID, ms.SURGERY_ID, 
CASE
WHEN nps.NBOCAP_ORDER IS NULL
THEN 21
ELSE nps.NBOCAP_ORDER
END NBOCAP_ORDER
FROM tblMAIN_SURGERY ms LEFT JOIN tblMAIN_REFERRALS mr
ON ms.CARE_ID = mr.CARE_ID
LEFT JOIN ltblNBOCAP_PREFERRED_SURGERY nps
ON ms.L_PROCEDURE = nps.PROC_ID AND
mr.N4_2_DIAGNOSIS_CODE = nps.NBOCAP_DIAGNOSIS
WHERE ms.CARE_ID IN (SELECT CARE_ID FROM vwNBOCAP_BASE_TUMOURS)
AND (ms.L_PROCEDURE IS NOT NULL AND ms.L_PROCEDURE <> '0') AND
ISDATE(ms.N7_9_SURGERY_DATE) = 1) u
GROUP BY CARE_ID
) tmin
ON t.CARE_ID = tmin.CARE_ID AND t.NBOCAP_ORDER = tmin.NBOCAP_ORDER
GROUP BY t.CARE_ID, t.NBOCAP_ORDER

Columns

View Definition

Possibly Referenced Tables/Views