INVESTIGATIONS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

N1_1_NHS_NUMBER

varchar

√

null

N1_2_HOSPITAL_NUMBER

varchar

√

null

N1_6_FORENAME

varchar

√

null

N1_5_SURNAME

varchar

√

null

L_CANCER_SITE

varchar

√

null

MOD_DESC

varchar

√

null

OTHER_MOD

varchar

√

null

L_REQUEST_DATE

smalldatetime

√

null

N3_2_IMAGING_DATE

smalldatetime

√

null

L_IM_RESULT

varchar

√

null

PATIENT_ID

int

√

null

CARE_ID

int

√

null

Consultant

varchar

√

null

MOD_CODE

varchar

√

null

N3_1_SITE_CODE

varchar

√

null

L_REPORT_DATE

smalldatetime

√

null

ModalityDesc

varchar

√

null

TabNumber

int

null

View Definition

CREATE VIEW [dbo].[INVESTIGATIONS]
AS
SELECT     TOP 100 PERCENT dbo.tblDEMOGRAPHICS.N1_1_NHS_NUMBER, dbo.tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER, 
                      dbo.tblDEMOGRAPHICS.N1_6_FORENAME, dbo.tblDEMOGRAPHICS.N1_5_SURNAME, dbo.tblMAIN_REFERRALS.L_CANCER_SITE, 
                      dbo.ltblIMAGING_MOD.MOD_DESC, ltblIMAGING_MOD_1.MOD_DESC AS OTHER_MOD, dbo.tblMAIN_IMAGING.L_REQUEST_DATE, 
                      dbo.tblMAIN_IMAGING.N3_2_IMAGING_DATE, dbo.tblMAIN_IMAGING.L_IM_RESULT, dbo.tblMAIN_REFERRALS.PATIENT_ID, 
                      dbo.tblMAIN_REFERRALS.CARE_ID, dbo.ltblCONSULTANTS.CON_CODE + '-' + dbo.ltblCONSULTANTS.CON_DESC AS Consultant, 
                      dbo.ltblIMAGING_MOD.MOD_CODE, dbo.tblMAIN_IMAGING.N3_1_SITE_CODE, dbo.tblMAIN_IMAGING.L_REPORT_DATE,
                      im.MOD_DESC AS ModalityDesc, ISNULL(vw.IsNonSiteSpecific, 0) AS TabNumber
FROM         dbo.ltblIMAGING_MOD RIGHT OUTER JOIN
                      dbo.ltblIMAGING_MOD ltblIMAGING_MOD_1 RIGHT OUTER JOIN
                      dbo.tblMAIN_IMAGING ON ltblIMAGING_MOD_1.MOD_CODE = dbo.tblMAIN_IMAGING.L_OTHER_IMAGING ON 
                      dbo.ltblIMAGING_MOD.MOD_CODE = dbo.tblMAIN_IMAGING.N3_3_MODALITY LEFT OUTER JOIN
                      dbo.ltblCONSULTANTS RIGHT OUTER JOIN
                      dbo.tblMAIN_REFERRALS INNER JOIN
                      dbo.tblDEMOGRAPHICS ON dbo.tblMAIN_REFERRALS.PATIENT_ID = dbo.tblDEMOGRAPHICS.PATIENT_ID ON 
                      dbo.ltblCONSULTANTS.NATIONAL_CODE = dbo.tblMAIN_REFERRALS.N2_7_CONSULTANT ON 
                      dbo.tblMAIN_IMAGING.CARE_ID = dbo.tblMAIN_REFERRALS.CARE_ID LEFT JOIN
                      dbo.ltblIMAGING_MOD im ON dbo.tblMAIN_IMAGING.MOD_ID = im.MOD_ID 
					  LEFT JOIN vwNonSiteSpecificCareIDs vw ON dbo.tblMAIN_REFERRALS.CARE_ID = vw.CareID                     
WHERE (dbo.ltblCONSULTANTS.IS_DELETED <> 1 OR dbo.ltblCONSULTANTS.IS_DELETED IS NULL)

Columns

View Definition

Possibly Referenced Tables/Views