BIvwUpperGIDiagnosisDetail

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

No of Liver Metastases

varchar

√

null

Cholangiocarcinoma Category

varchar

√

null

Cholangiocarcinoma Risk Factors

nvarchar

1073741823

√

null

View Definition

CREATE VIEW [BIvwUpperGIDiagnosisDetail]
AS
	WITH MappedRiskFactors AS (
		SELECT
			rcrf.CareID,
			crf.Description
		FROM
			ReferralCholangiocarcinomaRiskFactors rcrf
		INNER JOIN CholangiocarcinomaRiskFactors crf ON
			rcrf.CholangiocarcinomaRiskFactorID = crf.ID
	),
	DelimitedRiskFactors AS (
		SELECT DISTINCT CareID, STUFF(
				(SELECT ', ' + CAST(i.Description AS VARCHAR(50))
				FROM MappedRiskFactors i
				WHERE i.CareID = o.CareID
				FOR XML PATH (''))
				, 1, 1, '') AS RiskFactors
		FROM MappedRiskFactors o
	)
	SELECT
		rugi.CARE_ID,
		ltblLIVER_MET_QUANTITY.LIVER_MET_DESC AS [No of Liver Metastases],
		cholan.Description AS 'Cholangiocarcinoma Category',
		mrf.RiskFactors AS 'Cholangiocarcinoma Risk Factors'
	FROM
		tblREFERRAL_UPPER_GI rugi
	LEFT JOIN dbo.ltblLIVER_MET_QUANTITY ON
		rugi.NUM_LIVER_METS = ltblLIVER_MET_QUANTITY.LIVER_MET_ID
	LEFT JOIN ltblCholangiocarcinoma cholan ON
		rugi.CholangiocarcinomaCategory = cholan.ID
	LEFT JOIN DelimitedRiskFactors mrf ON
		mrf.CareID = rugi.CARE_ID

Columns

View Definition

Possibly Referenced Tables/Views