vwNBOCAP_BASE_CHEMORADIOTHERAPIES_POSTOP

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

√

null

EVENT_ID

int

√

null

EVENT_TYPE

varchar

√

null

View Definition

-- Created by Paul Stevens, February 2014

CREATE VIEW [dbo].[vwNBOCAP_BASE_CHEMORADIOTHERAPIES_POSTOP]
AS

SELECT t3.CARE_ID, t3.EVENT_ID, t3.EVENT_TYPE FROM
(SELECT t.CARE_ID, t.EVENT_DATE, MAX(t.EVENT_ID) AS EVENT_ID FROM
vwNBOCAP_CHEMOS_POSTOP t
JOIN
     (SELECT CARE_ID, MIN(EVENT_DATE) as manx
      FROM vwNBOCAP_CHEMOS_POSTOP
      GROUP BY CARE_ID) tmax
      ON t.CARE_ID = tmax.CARE_ID and t.EVENT_DATE = tmax.manx
GROUP BY t.CARE_ID, t.EVENT_DATE) t2
LEFT JOIN
(SELECT t.CARE_ID, t.EVENT_ID,t.EVENT_DATE, nbe2.EVENT_TYPE FROM
(SELECT CARE_ID, EVENT_ID, MAX(t2.EVENT_DATE) AS EVENT_DATE FROM
(SELECT nbe1.CARE_ID,nbe1.EVENT_ID,nbe1.EVENT_DATE,nbe1.EVENT_TYPE,ms.N7_9_SURGERY_DATE AS SURGERY_DATE
FROM vwNBOCAP_BASE_EVENTS_CHEMOS nbe1
LEFT JOIN
(SELECT nbs.CARE_ID, nbs.EVENT_ID FROM vwNBOCAP_BASE_SURGERIES nbs) t1
ON nbe1.CARE_ID = t1.CARE_ID
LEFT JOIN tblMAIN_SURGERY ms ON t1.EVENT_ID = ms.SURGERY_ID
WHERE nbe1.EVENT_DATE IS NOT NULL AND t1.CARE_ID IS NOT NULL AND
nbe1.EVENT_DATE > ms.N7_9_SURGERY_DATE) t2
GROUP BY CARE_ID, EVENT_ID) t
LEFT JOIN vwNBOCAP_BASE_EVENTS_CHEMOS nbe2
ON t.CARE_ID = nbe2.CARE_ID AND
t.EVENT_ID = nbe2.EVENT_ID) t3
ON t2.CARE_ID = t3.CARE_ID AND 
t2.EVENT_ID = t3.EVENT_ID

Columns

View Definition

Possibly Referenced Tables/Views