gzBREACH_UPGRADE_TREAT

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

N1_1_NHS_NUMBER

varchar

√

null

N1_10_DATE_BIRTH

datetime

16,3

√

null

L_CANCER_SITE

varchar

√

null

N_UPGRADE_DATE

smalldatetime

√

null

DECISION_DATE

smalldatetime

√

null

START_DATE

smalldatetime

√

null

ORG_CODE

varchar

√

null

TREATMENT_SETTING

varchar

√

null

N16_2_ADJ_DAYS

int

√

null

N16_4_ADJ_TREAT_CODE

int

√

null

ltblSYMPTOMS_HEAD_NECK_HANA_VALUES.Code

Implied ConstraintR

L_DIAGNOSIS

varchar

√

null

N4_2_DIAGNOSIS_CODE

varchar

√

null

N4_5_HISTOLOGY

varchar

√

null

N2_14_ADJ_TIME

int

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.gzBREACH_UPGRADE_TREAT
AS
SELECT     TOP (100) PERCENT dbo.tblDEMOGRAPHICS.N1_1_NHS_NUMBER, dbo.tblDEMOGRAPHICS.N1_10_DATE_BIRTH, 
                      dbo.tblMAIN_REFERRALS.L_CANCER_SITE, dbo.tblMAIN_REFERRALS.N_UPGRADE_DATE, dbo.tblDEFINITIVE_TREATMENT.DECISION_DATE, 
                      dbo.tblDEFINITIVE_TREATMENT.START_DATE, dbo.tblDEFINITIVE_TREATMENT.ORG_CODE, dbo.tblDEFINITIVE_TREATMENT.TREATMENT_SETTING, 
                      dbo.tblMAIN_REFERRALS.N16_2_ADJ_DAYS, dbo.tblMAIN_REFERRALS.N16_4_ADJ_TREAT_CODE, dbo.tblMAIN_REFERRALS.L_DIAGNOSIS, 
                      dbo.tblMAIN_REFERRALS.N4_2_DIAGNOSIS_CODE, dbo.tblMAIN_REFERRALS.N4_5_HISTOLOGY, dbo.tblMAIN_REFERRALS.N2_14_ADJ_TIME
FROM         dbo.tblDEMOGRAPHICS RIGHT OUTER JOIN
                      dbo.tblMAIN_REFERRALS ON dbo.tblDEMOGRAPHICS.PATIENT_ID = dbo.tblMAIN_REFERRALS.PATIENT_ID FULL OUTER JOIN
                      dbo.tblDEFINITIVE_TREATMENT ON dbo.tblMAIN_REFERRALS.CARE_ID = dbo.tblDEFINITIVE_TREATMENT.CARE_ID
WHERE     (dbo.tblDEFINITIVE_TREATMENT.START_DATE > { fn NOW() } - 30) AND (NOT (dbo.tblMAIN_REFERRALS.N2_13_CANCER_STATUS IN ('03', '07', '12', 
                      '13', '15', '18'))) AND (dbo.tblDEFINITIVE_TREATMENT.TREAT_NO IS NULL OR
                      dbo.tblDEFINITIVE_TREATMENT.TREAT_NO <> 2) AND (dbo.tblMAIN_REFERRALS.L_INAP_REF IS NULL OR
                      dbo.tblMAIN_REFERRALS.L_INAP_REF <> 1) AND (dbo.tblMAIN_REFERRALS.L_TUMOUR_STATUS <> 4) AND 
                      (dbo.tblMAIN_REFERRALS.L_TUMOUR_STATUS <> 5) AND (dbo.tblMAIN_REFERRALS.L_FIRST_APP IS NULL OR
                      dbo.tblMAIN_REFERRALS.L_FIRST_APP <> 1) AND (dbo.tblMAIN_REFERRALS.N_UPGRADE_DATE IS NOT NULL) OR
                      (dbo.tblDEFINITIVE_TREATMENT.START_DATE IS NULL) AND (NOT (dbo.tblMAIN_REFERRALS.N2_13_CANCER_STATUS IN ('03', '07', '12', '13', '15', 
                      '18'))) AND (dbo.tblDEFINITIVE_TREATMENT.TREAT_NO IS NULL OR
                      dbo.tblDEFINITIVE_TREATMENT.TREAT_NO <> 2) AND (dbo.tblMAIN_REFERRALS.L_INAP_REF IS NULL OR
                      dbo.tblMAIN_REFERRALS.L_INAP_REF <> 1) AND (dbo.tblMAIN_REFERRALS.L_TUMOUR_STATUS <> 4) AND 
                      (dbo.tblMAIN_REFERRALS.L_TUMOUR_STATUS <> 5) AND (dbo.tblMAIN_REFERRALS.L_FIRST_APP IS NULL OR
                      dbo.tblMAIN_REFERRALS.L_FIRST_APP <> 1) AND (dbo.tblMAIN_REFERRALS.N_UPGRADE_DATE BETWEEN { fn NOW() } - 62 AND { fn NOW() } - 18)
ORDER BY dbo.tblDEFINITIVE_TREATMENT.START_DATE DESC, dbo.tblMAIN_REFERRALS.L_CANCER_SITE

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views