ColorectalTrackerDetail

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ColorectalTrackerID

int

√

null

tblColorectalTracker.ColorectalTrackerID

Implied ConstraintR

CareID

int

√

null

NHSNumber

varchar

√

null

HospitalNumber

varchar

√

null

Name

varchar

112

√

null

DateofBirth

date

√

null

PostCode

varchar

√

null

PatientStatus

varchar

null

TrackingStatus

int

null

DateDiagnosis

smalldatetime

√

null

Diagnosis

varchar

158

√

null

OrganisationFollowUp

nvarchar

500

√

null

LatestFollowupDate

smalldatetime

√

null

LatestDateofReportedCTScan

smalldatetime

√

null

CTScanOutcome

varchar

√

null

CTScanNextDueDate

date

√

null

LatestDateofReportedColonoscopy

smalldatetime

√

null

ColonoscopyOutcome

varchar

√

null

ColonoscopyNextDueDate

date

√

null

LatestReportedTumourMarkerDate

smalldatetime

√

null

LatestCEAResult

varchar

255

√

null

CEAAlertLevel

real

√

null

LatestTrackingComment

text

2147483647

√

null

AlertLevel

real

√

null

GPName

varchar

100

√

null

GPAddress1

varchar

250

√

null

GPAddress2

varchar

250

√

null

GPAddress3

varchar

250

√

null

GPAddress4

varchar

250

√

null

GPPostCode

varchar

√

null

Consultant

varchar

√

null

LatestCEAResultDate

smalldatetime

√

null

CEAAboveAlert

int

null

ColonoscopyOverdue

int

null

CEAOverdue

int

null

CTScanOverdue

int

null

IsSuspended

int

null

ReasonSuspended

varchar

√

null

CEADueDate

date

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW [dbo].[ColorectalTrackerDetail]

AS
WITH OrderedCTScans as (
	SELECT 
		CARE_ID, 
		L_REPORT_DATE,
		L_IM_RESULT, 
		ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY L_REPORT_DATE DESC, IMAGE_ID DESC) r
	FROM tblMAIN_IMAGING 
	WHERE MOD_ID = 13
), OrderedColonoscopy as (
	SELECT 
		CARE_ID, 
		L_REPORT_DATE,
		L_IM_RESULT, 
		ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY L_REPORT_DATE DESC, IMAGE_ID DESC) r
	FROM tblMAIN_IMAGING 
	WHERE MOD_ID = 47
), TrackingNotes as (
	SELECT
	COM_ID,
	CARE_ID,
	COMMENTS,
	ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY DATE_TIME DESC, COM_ID DESC) r
	FROM tblTRACKING_COMMENTS
), Followups as(
	SELECT 
	CARE_ID,
	N14_1_ASSESSMENT_DATE,
	o.[Description],
	ROW_NUMBER() OVER(PARTITION BY CARE_ID ORDER BY N14_1_ASSESSMENT_DATE DESC, ASSESSMENT_ID DESC) r
	FROM
	tblMAIN_ASSESSMENT ma
	LEFT JOIN OrganisationSites o ON ma.R_ORG_CODE = o.Code
)

SELECT 
colo.ColorectalTrackerID AS ColorectalTrackerID,
colo.CareID AS CareID,
d.N1_1_NHS_NUMBER as NHSNumber,
d.N1_2_HOSPITAL_NUMBER as HospitalNumber,
CASE
	WHEN ISNULL(d.N1_5_SURNAME,'') <> '' AND ISNULL(d.N1_6_FORENAME,'') <> ''
	THEN ISNULL(d.N1_5_SURNAME,'') + ', ' + ISNULL(d.N1_6_FORENAME,'')	
	WHEN ISNULL(d.N1_5_SURNAME,'') <> '' AND ISNULL(d.N1_6_FORENAME,'') = ''
	THEN d.N1_5_SURNAME
	WHEN ISNULL(d.N1_5_SURNAME,'') = '' AND ISNULL(d.N1_6_FORENAME,'') <> ''
	THEN d.N1_6_FORENAME
	ELSE NULL
END Name,
d.N1_10_DATE_BIRTH as DateofBirth,
d.N1_8_POSTCODE as PostCode,
CASE
	WHEN ISNULL(d.L_DEATH_STATUS,0) = 1
	THEN 'Dead'
	ELSE 'Alive'
END PatientStatus,
CASE
	WHEN ISNULL(stat.TrackingStatusDesc, 'Active') = 'Suspended'
	THEN 1
	ELSE 0
END TrackingStatus,
mr.N4_1_DIAGNOSIS_DATE as DateDiagnosis,
mr.L_DIAGNOSIS + ' - ' + diag.DIAG_DESC as Diagnosis,
ma.[Description] as OrganisationFollowUp,
ma.N14_1_ASSESSMENT_DATE as LatestFollowupDate,
OrderedCTScans.L_REPORT_DATE as LatestDateofReportedCTScan,
OrderedCTScans.L_IM_RESULT as CTScanOutcome,
colo.CTScanNextDueDate AS CTScanNextDueDate,
OrderedColonoscopy.L_REPORT_DATE AS LatestDateofReportedColonoscopy ,
OrderedColonoscopy.L_IM_RESULT AS ColonoscopyOutcome,
colo.ColonoscopyNextDueDate AS ColonoscopyNextDueDate,
tm.ImagingDate AS LatestReportedTumourMarkerDate,
CASE
	WHEN ISNULL(ISNUMERIC(mi.L_CEA_TM_RESULT), -1) = 1
	THEN mi.L_CEA_TM_RESULT
	ELSE NULL			
END LatestCEAResult,
colo.AlertLevel AS CEAAlertLevel,
tn.COMMENTS as LatestTrackingComment,
colo.AlertLevel,
GP.Name as GPName,
Practice.AddressLine1 as GPAddress1,
Practice.AddressLine2 as GPAddress2,
Practice.AddressLine3 as GPAddress3,
Practice.AddressLine4 as GPAddress4,
Practice.Postcode as GPPostCode,
cons.CON_DESC as Consultant,
tm.ImagingDate AS LatestCEAResultDate,
CASE
	WHEN ISNULL(ISNUMERIC(mi.L_CEA_TM_RESULT), -1) = 1
	THEN
		CASE
			WHEN CAST(mi.L_CEA_TM_RESULT AS REAL) >= CAST(colo.AlertLevel AS REAL)
			THEN 1
			ELSE 0
		END
	ELSE 0			
END CEAAboveAlert,
CASE
	WHEN colo.ColonoscopyNextDueDate IS NOT NULL
	THEN
		CASE
			WHEN colo.ColonoscopyNextDueDate <= GETDATE()
			THEN 1
			ELSE 0
		END
	ELSE 0
END ColonoscopyOverdue,
CASE
	WHEN colo.CEADueDate IS NOT NULL
	THEN
		CASE
			WHEN colo.CEADueDate <= GETDATE()
			THEN 1
			ELSE 0
		END
	ELSE 0
END CEAOverdue,
CASE
	WHEN colo.CTScanNextDueDate IS NOT NULL
	THEN
		CASE
			WHEN colo.CTScanNextDueDate <= GETDATE()
			THEN 1
			ELSE 0
		END
	ELSE 0
END CTScanOverdue,
CASE
	WHEN ISNULL(stat.TrackingStatusDesc, 'Active') = 'Suspended'
	THEN 1
	ELSE 0
END IsSuspended,
colo.ReasonSuspended,
colo.CEADueDate
FROM 
(SELECT CareID, MAX(ColorectalTrackerID) AS MaxColorectalTrackerID FROM tblColorectalTracker GROUP BY CareID) maxColo
LEFT JOIN tblColorectalTracker colo
ON maxColo.MaxColorectalTrackerID = colo.ColorectalTrackerID
LEFT JOIN ltblTrackingStatus stat
ON colo.TrackingStatusID = stat.TrackingStatusID
INNER JOIN tblREFERRAL_COLORECTAL coloRef
ON colo.CareID = coloRef.CARE_ID
INNER JOIN tblMAIN_REFERRALS mr
ON coloRef.CARE_ID = mr.CARE_ID
LEFT JOIN tblDEMOGRAPHICS d
ON mr.PATIENT_ID = d.PATIENT_ID
LEFT JOIN ltblSTATUS s on mr.N2_13_CANCER_STATUS = s.STATUS_CODE
LEFT JOIN Followups ma on ma.CARE_ID = mr.CARE_ID and ma.r = 1
LEFT JOIN GP on GP.ID = d.RegisteredGP
LEFT JOIN Practice ON Practice.ID = d.RegisteredPractice
LEFT JOIN OrderedCTScans on mr.CARE_ID = OrderedCTScans.CARE_ID and OrderedCTScans.r=1
LEFT JOIN OrderedColonoscopy on mr.CARE_ID = OrderedColonoscopy.CARE_ID and OrderedColonoscopy.r=1
LEFT JOIN TrackingNotes tn on tn.CARE_ID = mr.CARE_ID and tn.r = 1
LEFT JOIN ltblDIAGNOSIS diag on mr.L_DIAGNOSIS = diag.DIAG_CODE
LEFT JOIN ConsultantsByCode cons on cons.NATIONAL_CODE = mr.N2_7_CONSULTANT 
LEFT JOIN 
(SELECT mi1.CARE_ID, mi1.N3_2_IMAGING_DATE AS ImagingDate, mi1.MOD_ID, MAX(mi1.IMAGE_ID) AS MaxImageID
FROM tblMAIN_IMAGING mi1 
		JOIN tblMAIN_IMAGING mi2
		ON mi1.IMAGE_ID = mi2.IMAGE_ID AND
		ISNULL(ISNUMERIC(mi1.L_CEA_TM_RESULT), -1) = 1
		JOIN
     (SELECT CARE_ID, MOD_ID, MAX(N3_2_IMAGING_DATE) AS manx
      FROM tblMAIN_IMAGING
      GROUP BY CARE_ID, MOD_ID
      HAVING MOD_ID = 94) tmax
      ON mi1.CARE_ID = tmax.CARE_ID AND 
      mi1.MOD_ID = tmax.MOD_ID AND
      mi1.N3_2_IMAGING_DATE = tmax.manx 
GROUP BY mi1.CARE_ID, mi1.MOD_ID, mi1.N3_2_IMAGING_DATE
HAVING mi1.MOD_ID = 94) tm
ON colo.CareID = tm.CARE_ID
LEFT JOIN tblMAIN_IMAGING mi
ON tm.MaxImageID = mi.IMAGE_ID

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views