BIvwUrologyDiagnosisDetail

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

Lung Mets Sub Stage

varchar

√

null

RMH Stage Grouping

varchar

√

null

Extra Nodal Mets Field 1

varchar

√

null

Extra Nodal Mets Field 2

varchar

√

null

Extra Nodal Mets Field 3

varchar

√

null

Extra Nodal Mets Field 4

varchar

√

null

Extra Nodal Mets Field 5

varchar

√

null

eGFR at Diagnosis

int

√

null

Hydronephrosis Code

varchar

√

null

LDH Level

int

√

null

S Category

varchar

√

null

S Category AFP

int

√

null

S Category HCG

int

√

null

S Category LDH

int

√

null

PSA at Diagnosis

varchar

√

null

Biopsy Gleason

varchar

√

null

Biopsy Gleason Tertiary Score

int

√

null

Final Gleason

varchar

√

null

Final Gleason Tertiary Score

int

√

null

Risk Category

varchar

√

null

View Definition

CREATE VIEW [dbo].[BIvwUrologyDiagnosisDetail]
		AS
		SELECT				  dbo.tblREFERRAL_UROLOGY.CARE_ID, 
							  dbo.ltblLUNG_METS_SUB_STAGE.SUB_STAGE_VALUE AS [Lung Mets Sub Stage], 
							  dbo.ltblRMH_STAGE.RMH_VALUE AS [RMH Stage Grouping], 
							  CASE SUBSTRING(tblREFERRAL_UROLOGY.EXTRA_NODAL_METS, 1, 1) 
								  WHEN '1' THEN 'Liver Involvement' 
								  WHEN '2' THEN 'Brain Involvement' 
								  WHEN '3' THEN 'Mediastinal Involvement' 
								  WHEN '4' THEN 'Neck Nodes' 
								  WHEN '5' THEN 'Lung Involvement'
								  ELSE NULL 
							  END AS [Extra Nodal Mets Field 1],
							  CASE SUBSTRING(tblREFERRAL_UROLOGY.EXTRA_NODAL_METS, 3, 1) 
								  WHEN '1' THEN 'Liver Involvement' 
								  WHEN '2' THEN 'Brain Involvement' 
								  WHEN '3' THEN 'Mediastinal Involvement' 
								  WHEN '4' THEN 'Neck Nodes' 
								  WHEN '5' THEN 'Lung Involvement'
								  ELSE NULL 
							  END AS [Extra Nodal Mets Field 2],
							  CASE SUBSTRING(tblREFERRAL_UROLOGY.EXTRA_NODAL_METS, 5, 1) 
								  WHEN '1' THEN 'Liver Involvement' 
								  WHEN '2' THEN 'Brain Involvement' 
								  WHEN '3' THEN 'Mediastinal Involvement' 
								  WHEN '4' THEN 'Neck Nodes' 
								  WHEN '5' THEN 'Lung Involvement'
							      ELSE NULL 
							  END AS [Extra Nodal Mets Field 3],
							  CASE SUBSTRING(tblREFERRAL_UROLOGY.EXTRA_NODAL_METS, 7, 1) 
								  WHEN '1' THEN 'Liver Involvement' 
								  WHEN '2' THEN 'Brain Involvement' 
								  WHEN '3' THEN 'Mediastinal Involvement' 
								  WHEN '4' THEN 'Neck Nodes' 
								  WHEN '5' THEN 'Lung Involvement'
							      ELSE NULL 
							  END AS [Extra Nodal Mets Field 4],
							  CASE SUBSTRING(tblREFERRAL_UROLOGY.EXTRA_NODAL_METS, 9, 1) 
								  WHEN '1' THEN 'Liver Involvement' 
								  WHEN '2' THEN 'Brain Involvement' 
								  WHEN '3' THEN 'Mediastinal Involvement' 
								  WHEN '4' THEN 'Neck Nodes' 
								  WHEN '5' THEN 'Lung Involvement'
							      ELSE NULL 
							  END AS [Extra Nodal Mets Field 5],
							  dbo.tblREFERRAL_UROLOGY.EGFR AS [eGFR at Diagnosis], 
							  dbo.ltblHYDRONEPHROSIS.HydroDesc AS [Hydronephrosis Code], dbo.tblREFERRAL_UROLOGY.LDHNormal AS [LDH Level], 
							  dbo.tblREFERRAL_UROLOGY.N_U2_PRE_S_STAGE AS [S Category], 
							  dbo.tblREFERRAL_UROLOGY.SCATEGORY_AFP AS [S Category AFP], 
							  dbo.tblREFERRAL_UROLOGY.SCATEGORY_HCG AS [S Category HCG], 
							  dbo.tblREFERRAL_UROLOGY.SCATEGORY_LDH AS [S Category LDH], 
							  dbo.tblREFERRAL_UROLOGY.N_U1_PSA AS [PSA at Diagnosis],
							  NULLIF(ISNULL(CAST(dbo.tblREFERRAL_UROLOGY.R_BIOPSY_SCORE_1 AS varchar(1)), '') + ' + ' + ISNULL(CAST(dbo.tblREFERRAL_UROLOGY.R_BIOPSY_SCORE_2 AS varchar(1)), ''), ' + ') AS 'Biopsy Gleason',
							  R_BIOPSY_TERT_SCORE AS 'Biopsy Gleason Tertiary Score',
							  NULLIF(ISNULL(CAST(dbo.tblREFERRAL_UROLOGY.R_GLEASON_SCORE_1 AS varchar(1)), '') + ' + ' + ISNULL(CAST(dbo.tblREFERRAL_UROLOGY.R_GLEASON_SCORE_2 AS varchar(1)), ''), ' + ') AS 'Final Gleason',
							  R_GLEASON_TERT_SCORE AS 'Final Gleason Tertiary Score',
							  ltblRiskCatagory.value AS 'Risk Category'
		FROM				  dbo.tblREFERRAL_UROLOGY JOIN
							  dbo.tblMAIN_REFERRALS ON dbo.tblMAIN_REFERRALS.CARE_ID = tblREFERRAL_UROLOGY.CARE_ID LEFT OUTER JOIN
							  dbo.ltblHYDRONEPHROSIS ON dbo.tblREFERRAL_UROLOGY.HYDRONEPHROSIS = dbo.ltblHYDRONEPHROSIS.HydroCode LEFT OUTER JOIN
							  dbo.ltblRMH_STAGE ON dbo.tblREFERRAL_UROLOGY.RMH_STAGE = dbo.ltblRMH_STAGE.RMH_ID LEFT OUTER JOIN
							  dbo.ltblLUNG_METS_SUB_STAGE ON dbo.tblREFERRAL_UROLOGY.LUNG_METS_SUB_STAGE = dbo.ltblLUNG_METS_SUB_STAGE.SUB_STAGE_ID
							  LEFT OUTER JOIN ltblRiskCatagory
							  ON ltblRiskCatagory.ID = tblREFERRAL_UROLOGY.RISK_CATAGORY

Columns

View Definition

Possibly Referenced Tables/Views