FasterDiagnosis

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CareID

int

null

TargetDate

smalldatetime

√

null

PathwayEndDate

datetime

16,3

√

null

CalculationDateType

varchar

√

null

ReportDate

smalldatetime

√

null

DaysInPathway

int

√

null

Adjustment

int

√

null

EndReasonID

int

√

null

EndReasonDesc

nvarchar

√

null

DelayReasonID

int

√

null

DelayReasonDesc

nvarchar

320

√

null

DelayComments

nvarchar

1073741823

√

null

ExclusionReasonID

int

√

null

ExclusionReasonDesc

nvarchar

160

√

null

FasterDiagnosisCancerSiteID

int

√

null

FasterDiagnosisCancerSiteDesc

nvarchar

100

√

null

HasCancerSiteOverride

bit

√

null

FasterDiagnosisOrganisationID

int

√

null

FasterDiagnosisOrganisationCode

varchar

√

null

FasterDiagnosisOrganisationDesc

nvarchar

500

√

null

CommunicationMethodID

int

√

null

CommunicationMethodDesc

nvarchar

166

√

null

FasterDiagnosisInformingCareProfessionalID

int

√

null

CareProfessional

nvarchar

246

√

null

DecisionDate

datetime

16,3

√

null

DecisionOrg

varchar

√

null

View Definition

CREATE VIEW [dbo].[FasterDiagnosis]
AS
WITH PathwayEndReasonsAndDates AS (
	SELECT
		mr.CARE_ID AS CareID,
		CASE
			WHEN mr.FasterDiagnosisExclusionDate IS NOT NULL AND mr.L_PT_INFORMED_DATE IS NULL THEN 3 --Excluded
			WHEN mr.L_PT_INFORMED_DATE IS NOT NULL THEN
				CASE
					WHEN LEFT(mr.N4_2_DIAGNOSIS_CODE, 3) = 'C44' AND mr.SNOMed_CT IN(641, 642, 643, 644, 645, 646, 652) THEN 2 --BCC
					WHEN LEFT(mr.N4_2_DIAGNOSIS_CODE, 1) = 'D' AND LEFT(mr.N4_2_DIAGNOSIS_CODE, 3) <> 'D05' THEN 2 --D-Code
					WHEN LEFT(mr.N4_2_DIAGNOSIS_CODE, 3) = 'E85' THEN 2 --Amyloidosis
					WHEN L_TUMOUR_STATUS = 3 OR N2_13_CANCER_STATUS IN('03', '69') THEN 
						CASE WHEN mr.FDPlannedInterval = 1 THEN 4 ELSE 2 END--Non-cancer status
					WHEN mr.SNOMed_CT IN(597, 725) THEN 1 --Neuroendocrine
					WHEN d.FasterDiagnosisCancerSiteID IS NOT NULL THEN 1 --Cancer with site
					WHEN LEFT(mr.N4_2_DIAGNOSIS_CODE, 3) = 'C17' THEN 1 --Can't tell if this is colo or UGI, but it is a cancer
				END
			ELSE NULL
		END AS ReasonID,
		mr.FasterDiagnosisExclusionDate AS ExclusionDate,
		mr.L_PT_INFORMED_DATE AS InformedDate
	FROM
		tblMAIN_REFERRALS mr
	LEFT JOIN ltblDIAGNOSIS d ON
		d.DIAG_CODE = mr.N4_2_DIAGNOSIS_CODE
), AllTreatments AS (
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_SurgerySupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_ChemotherapySupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_TeletherapySupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_BrachytherapySupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_PalliativeSupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_MonitoringSupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_OtherSupport WITH (NOEXPAND) UNION ALL
	SELECT CARE_ID, DecisionDate, DecisionOrg, IsDefinitive, TreatID, Tiebreak FROM IxView_FD_DeclinedSupport WITH (NOEXPAND)

), NumberedTreatments AS (
	SELECT
		per.CareID,
		per.ExclusionDate,
		per.InformedDate,
		per.ReasonID,
		at.DecisionDate,
		at.DecisionOrg,
		ROW_NUMBER() OVER (PARTITION BY CareID ORDER BY IsDefinitive DESC, DecisionDate ASC, TreatID ASC, Tiebreak ASC) r
	FROM
		PathwayEndReasonsAndDates per
	LEFT JOIN AllTreatments at ON
		at.CARE_ID = per.CareID
		AND (at.IsDefinitive = 1 OR ReasonID = 2)
), CalculationDates AS (
	SELECT
		endReasons.CareID,
		endReasons.ReasonID,
		CASE 
			WHEN endReasons.ReasonID = 3 THEN endReasons.ExclusionDate
			ELSE CASE
				WHEN endReasons.DecisionDate < endReasons.InformedDate OR endReasons.InformedDate IS NULL THEN endReasons.DecisionDate
				ELSE endReasons.InformedDate
			END
		END AS CalculationDate,
		CASE 
			WHEN endReasons.ReasonID = 3 THEN 'Exclusion Date'
			ELSE CASE
				WHEN endReasons.DecisionDate < endReasons.InformedDate OR endReasons.InformedDate IS NULL THEN 'Decision to Treat Date'
				ELSE 'Date Patient Informed'
			END
		END AS CalculationDateType,
		CASE endReasons.ReasonID
			WHEN 4 THEN endReasons.InformedDate
			WHEN 3 THEN endReasons.ExclusionDate
			WHEN 2 THEN endReasons.InformedDate
			WHEN 1 THEN endReasons.InformedDate
		END AS ReportDate,
		DecisionDate,
		DecisionOrg
	FROM NumberedTreatments endReasons
	WHERE r = 1
), GatherData AS (
	SELECT
		mr.CARE_ID AS CareID,
		mr.N2_6_RECEIPT_DATE AS ReceiptDate,
		ISNULL(mr.L_PT_INFORMED_DATE, mr.FasterDiagnosisExclusionDate) AS ReportDate,
		CalcDates.CalculationDate,
		CASE WHEN CalcDates.CalculationDate IS NULL THEN NULL else CalculationDateType end as CalculationDateType,
		DATEDIFF(d, mr.N2_6_RECEIPT_DATE, ISNULL(CalcDates.CalculationDate, GETDATE())) AS DaysInPathway,
		CalcDates.ReasonID AS EndReasonID,
		fdper.Description AS EndReasonDesc,
		mr.FasterDiagnosisDelayReasonID AS DelayReasonID,
		fddr.Description AS DelayReasonDesc,
		mr.FasterDiagnosisDelayReasonComments AS DelayComments,
		mr.FasterDiagnosisExclusionReasonID AS ExclusionReasonID,
		fder.Description AS ExclusionReasonDesc,
		fdcs.ID AS FasterDiagnosisCancerSiteID,
		fdcs.Description AS FasterDiagnosisCancerSiteDesc,
		o.ID AS FasterDiagnosisOrganisationID,
		o.Code AS FasterDiagnosisOrganisationCode,
		o.[Description] AS FasterDiagnosisOrganisationDesc,
		mr.FasterDiagnosisCommunicationMethodID AS CommunicationMethodID,
		fdcm.Description +  ISNULL( ' - ' + mr.FasterDiagnosisOtherCommunicationMethod, '') AS CommunicationMethodDesc,
		mr.FasterDiagnosisInformingCareProfessionalID,
		cp.Description + ISNULL(' - ' + mr.FasterDiagnosisOtherCareProfessional, '') AS CareProfessional,
		CASE
			WHEN mr.N2_15_ADJ_REASON = 3 AND mr.L_CANCELLED_DATE IS NOT NULL
				THEN DATEDIFF(d, mr.N2_6_RECEIPT_DATE, mr.L_CANCELLED_DATE)
			ELSE 0
		END AS Adjustment,
		CAST(CASE
			WHEN mr.FasterDiagnosisCancerSiteOverrideID IS NOT NULL THEN 1
			ELSE 0
		END AS BIT) AS HasCancerSiteOverride,
		CalcDates.DecisionDate,
		CalcDates.DecisionOrg
	FROM
		tblMAIN_REFERRALS mr
	INNER JOIN tblDEMOGRAPHICS demo ON
		demo.PATIENT_ID = mr.PATIENT_ID
	LEFT JOIN CalculationDates CalcDates ON
		mr.CARE_ID = CalcDates.CareID
	LEFT JOIN ltblFasterDiagnosisPathwayEndReason fdper ON
		fdper.ID = CalcDates.ReasonID
	LEFT JOIN ltblFasterDiagnosisDelayReason fddr ON
		fddr.ID = mr.FasterDiagnosisDelayReasonID
	LEFT JOIN ltblFasterDiagnosisExclusionReason fder ON
		fder.ID = mr.FasterDiagnosisExclusionReasonID AND fder.IsDeleted = 0
	LEFT JOIN ltblDIAGNOSIS d ON
		d.DIAG_CODE = mr.N4_2_DIAGNOSIS_CODE
	LEFT JOIN ltblFasterDiagnosisCancerSite fdcs ON
		fdper.CWTCode = '01' AND
		fdcs.ID = ISNULL(mr.FasterDiagnosisCancerSiteOverrideID, CASE
																	WHEN dbo.GetAge(demo.N1_10_DATE_BIRTH, mr.N2_6_RECEIPT_DATE) < 16 THEN 2
																	WHEN mr.SNOMed_CT IN(597, 725) THEN 20
																	WHEN LEFT(mr.N4_2_DIAGNOSIS_CODE, 3) = 'C17' THEN
																		CASE
																			WHEN mr.L_CANCER_SITE = 'Upper GI' THEN 6
																			WHEN mr.L_CANCER_SITE = 'Colorectal' THEN 7
																		END
																	ELSE d.FasterDiagnosisCancerSiteID
	END)
	LEFT JOIN OrganisationSites o ON
		o.ID = mr.FasterDiagnosisOrganisationID
	LEFT JOIN ltblFasterDiagnosisCommunicationMethod fdcm ON
		fdcm.ID = mr.FasterDiagnosisCommunicationMethodID
	LEFT JOIN ltblCareProfessional cp ON
		cp.ID = mr.FasterDiagnosisInformingCareProfessionalID
	WHERE
		(N2_4_PRIORITY_TYPE = '03' OR (N2_16_OP_REFERRAL = '17' AND N2_4_PRIORITY_TYPE = '02'))
		AND mr.TRANSFER_REASON IS NULL
		AND ISNULL(mr.L_FIRST_APP, 0) <> 1
		-- Do not include patients excluded because died before 28 days
			AND NOT (
			CalcDates.ReasonID IS NOT NULL
			AND CalcDates.ReasonID = 3
			AND mr.FasterDiagnosisExclusionReasonID = 1
			AND CalcDates.CalculationDate IS NOT NULL
			AND (DateDiff(d,  mr.N2_6_RECEIPT_DATE, CalcDates.CalculationDate) <= 28)
		)
), FinalPass AS (
SELECT
		gd.CareID,
		DATEADD(day, 28 + Adjustment, gd.ReceiptDate) AS TargetDate,
		gd.ReportDate,
		gd.CalculationDate,
		CalculationDateType,
		DATEDIFF(d, gd.ReceiptDate, ISNULL(gd.CalculationDate, GETDATE())) - gd.Adjustment AS DaysInPathway,
		gd.Adjustment,
		gd.EndReasonID,
		gd.EndReasonDesc,
		gd.DelayReasonID,
		gd.DelayReasonDesc,
		gd.DelayComments,
		gd.ExclusionReasonID,
		gd.ExclusionReasonDesc,
		gd.FasterDiagnosisCancerSiteID,
		gd.FasterDiagnosisCancerSiteDesc,
		gd.HasCancerSiteOverride,
		gd.FasterDiagnosisOrganisationID,
		gd.FasterDiagnosisOrganisationCode,
		gd.FasterDiagnosisOrganisationDesc,
		gd.CommunicationMethodID,
		gd.CommunicationMethodDesc,
		gd.FasterDiagnosisInformingCareProfessionalID,
		gd.CareProfessional,
		gd.DecisionDate,
		gd.DecisionOrg
	FROM
		GatherData gd
)
SELECT
	CareID,
	TargetDate,
	CalculationDate AS PathwayEndDate,
	CalculationDateType,
	ReportDate AS ReportDate,
	DaysInPathway,
	Adjustment,
	EndReasonID,
	EndReasonDesc,
	DelayReasonID,
	DelayReasonDesc,
	DelayComments,
	ExclusionReasonID,
	ExclusionReasonDesc,
	FasterDiagnosisCancerSiteID,
	FasterDiagnosisCancerSiteDesc,
	HasCancerSiteOverride,
	FasterDiagnosisOrganisationID,
	FasterDiagnosisOrganisationCode,
	FasterDiagnosisOrganisationDesc,
	CommunicationMethodID,
	CommunicationMethodDesc,
	FasterDiagnosisInformingCareProfessionalID,
	CareProfessional,
	DecisionDate,
	DecisionOrg
FROM
	FinalPass

Columns

View Definition

Possibly Referenced Tables/Views