BreastTrackerDetail

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

BreastTrackerID

int

√

null

tblBreastTracker.BreastTrackerID

Implied ConstraintR

CareID

int

√

null

NHSNumber

varchar

√

null

HospitalNumber

varchar

√

null

FullName

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

LatestDateofReportedMammogram

smalldatetime

√

null

MammogramOutcome

varchar

√

null

MammogramNextDueDate

date

√

null

ACDStartDate

smalldatetime

√

null

ACDFinishDate

smalldatetime

√

null

ACDDrugType

varchar

√

null

ACDDrugRegimenAcronym

varchar

255

√

null

Aromatase

varchar

√

null

LastDateofreportedDexaScan

smalldatetime

√

null

DexaScanOutcome

varchar

√

null

DEXAScanNextDueDate

date

√

null

LatestTrackingComment

text

2147483647

√

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

MammogramOverdue

int

null

DEXAScanOverdue

int

null

IsSuspended

int

null

ReasonSuspended

varchar

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW [dbo].[BreastTrackerDetail]

AS
WITH 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
), OrderedMamagram 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 = 30
), OrderedACD as (
	SELECT
		mc.CARE_ID,
		mc.N9_10_START_DATE,
		mc.L_END_DATE,
		dt.DRUG_TYPE_DESC AS DrugTherapyType,
		mc.N9_9_DRUG_REGIMEN,
		mc.L_AROMATASE,
		ROW_NUMBER() OVER (PARTITION BY mc.CARE_ID ORDER BY mc.N9_10_START_DATE DESC, mc.CHEMO_ID DESC) r
	FROM tblMAIN_CHEMOTHERAPY mc
	LEFT JOIN ltblDRUG_TYPE dt ON mc.N9_7_THERAPY_TYPE = dt.DRUG_TYPE_CODE
), OrderedDEXAScan 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 = 51
), 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
)
SELECT 
bt.BreastTrackerID AS BreastTrackerID,
bt.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 FullName,
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,
OrderedMamagram.L_REPORT_DATE as LatestDateofReportedMammogram, 
OrderedMamagram.L_IM_RESULT as MammogramOutcome,
bt.MammogramNextDueDate AS MammogramNextDueDate,
OrderedACD.N9_10_START_DATE as ACDStartDate,
OrderedACD.L_END_DATE as ACDFinishDate,
OrderedACD.DrugTherapyType as ACDDrugType,
OrderedACD.N9_9_DRUG_REGIMEN as ACDDrugRegimenAcronym,
CASE OrderedACD.L_AROMATASE
	WHEN 1 then 'Yes'
	WHEN 2 then 'No'
	ELSE NULL
END as Aromatase,
OrderedDEXAScan.L_REPORT_DATE as LastDateofreportedDexaScan,
OrderedDEXAScan.L_IM_RESULT as DexaScanOutcome,
bt.DexaScanNextDueDate AS DEXAScanNextDueDate,
tn.COMMENTS as LatestTrackingComment,
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,
CASE
	WHEN bt.MammogramNextDueDate <= GETDATE()
	THEN 1
	ELSE 0
END MammogramOverdue,
CASE
	WHEN bt.DexaScanNextDueDate <= GETDATE()
	THEN 1
	ELSE 0
END DEXAScanOverdue,
CASE
	WHEN ISNULL(stat.TrackingStatusDesc, 'Active') = 'Suspended'
	THEN 1
	ELSE 0
END IsSuspended,
bt.ReasonSuspended
FROM (SELECT CareID, MAX(BreastTrackerID) AS MaxBreastTrackerID FROM tblBreastTracker GROUP BY CareID) maxBreast
LEFT JOIN tblBreastTracker bt
ON maxBreast.MaxBreastTrackerID = bt.BreastTrackerID
LEFT JOIN ltblTrackingStatus stat
ON bt.TrackingStatusID = stat.TrackingStatusID
INNER JOIN tblREFERRAL_BREAST breastRef
ON bt.CareID = breastRef.CARE_ID
INNER JOIN tblMAIN_REFERRALS mr
ON breastRef.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 OrderedMamagram on OrderedMamagram.CARE_ID = mr.CARE_ID and OrderedMamagram.r = 1
LEFT JOIN OrderedACD on OrderedACD.CARE_ID = mr.CARE_ID and OrderedACD.r = 1
LEFT JOIN OrderedDEXAScan on OrderedDEXAScan.CARE_ID = mr.CARE_ID and OrderedDEXAScan.r = 1
LEFT JOIN TrackingNotes tn on tn.CARE_ID = mr.CARE_ID and tn.r = 1
LEFT JOIN GP on GP.ID = d.RegisteredGP
LEFT JOIN Practice ON Practice.ID = d.RegisteredPractice
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 t.CARE_ID, t.N3_2_IMAGING_DATE AS ImagingDate, t.MOD_ID, MAX(t.IMAGE_ID) AS MaxImageID
FROM tblMAIN_IMAGING t 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 = 30) tmax
      ON t.CARE_ID = tmax.CARE_ID AND 
      t.MOD_ID = tmax.MOD_ID AND
      t.MOD_ID = 30 AND
      t.N3_2_IMAGING_DATE = tmax.manx 
GROUP BY t.CARE_ID, t.MOD_ID, t.N3_2_IMAGING_DATE) mammogram
ON bt.CareID = mammogram.CARE_ID
LEFT JOIN tblMAIN_IMAGING mi
ON mammogram.MaxImageID = mi.IMAGE_ID

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views