BIvwSkinPathology

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CARE_ID

int

null

Cancer Site

varchar

√

null

NHS Number

varchar

null

Hospital Number

varchar

√

null

Full Name

varchar

111

√

null

Date of Receipt

smalldatetime

√

null

Date of Reporting

smalldatetime

√

null

Report No

varchar

√

null

Investigation Type

varchar

√

null

Nature of Specimen

varchar

√

null

Report Status

varchar

√

null

Surgeon

varchar

√

null

Requesting Organisation

nvarchar

500

√

null

Pathologist

varchar

√

null

Reporting Organisation

nvarchar

500

√

null

Diagnosis Site (ICD)

varchar

203

√

null

Diagnosis Site (SNOMED)

nvarchar

566

√

null

Tumour Laterality

varchar

√

null

Specimen Type

varchar

√

null

Skin Cancer Lesion Number

varchar

√

null

Size of Specimen

varchar

8000

√

null

Max Tumour Diameter (mm)

real

√

null

Nodule

varchar

√

null

Size of Nodule (mm)

varchar

8000

√

null

Border of Lesion

varchar

√

null

Pigmentation of Lesion

varchar

√

null

No of Nodes Examined

int

√

null

No of Positive Nodes

int

√

null

Type (SNOMed)

varchar

133

√

null

Type of Growth Pattern

varchar

8000

√

null

Grade of Differentiation

varchar

105

√

null

Synchronous Tumour

varchar

√

null

Excision Margins

varchar

100

√

null

Invasion Laterality

varchar

√

null

Peripheral Margin

varchar

√

null

Distance to Nearest Peripheral Margin (mm)

real

√

null

Deep Margin

varchar

√

null

Distance to Nearest Deep (mm)

real

√

null

Vascular/Lymphatic Invasion

varchar

100

√

null

Ulceration

real

√

null

BRAF Status

varchar

√

null

Perineural Invasion

varchar

√

null

Radial Growth Phase

varchar

√

null

Vertical Growth Phase

varchar

√

null

Regression

varchar

√

null

Microsatellites

varchar

√

null

Co-existing Naevus

varchar

√

null

Dysplastic

varchar

√

null

Mitotic Rate

real

√

null

Infiltrating Lymphocytes

varchar

√

null

Breslow Thickness (mm)

varchar

√

null

Clarks Level

varchar

100

√

null

AJCC Staging

varchar

√

null

Staging

varchar

√

null

TNM Stage Grouping (Pathological)

varchar

√

null

Pathology Report Text

varchar

2147483647

√

null

View Definition

CREATE VIEW [dbo].[BIvwSkinPathology]
AS
SELECT
	tblMAIN_PATHOLOGY.CARE_ID												AS 'CARE_ID',
	tblMAIN_REFERRALS.L_CANCER_SITE											AS 'Cancer Site',
	tblDEMOGRAPHICS.N1_1_NHS_NUMBER											AS 'NHS Number',
	tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER									AS 'Hospital Number',
	tblDEMOGRAPHICS.N1_6_FORENAME +' '+ tblDEMOGRAPHICS.N1_5_SURNAME		AS 'Full Name',
	tblMAIN_PATHOLOGY.N8_2_RECEIPT_DATE										AS 'Date of Receipt',
	tblMAIN_PATHOLOGY.N8_3_RESULT_DATE										AS 'Date of Reporting',
	tblMAIN_PATHOLOGY.N8_20_REPORT_NUMBER									AS 'Report No',
	ltblINVESTIGATION_TYPE.TYPE_DESC										AS 'Investigation Type',
	ltblSPECIMEN.SPECIMEN_DESC												AS 'Nature of Specimen',
	ltblREPORT_STATUS.STATUS_DESC											AS 'Report Status',		
	ConsultantsByCode.CON_DESC +' - '+ ConsultantsByCode.CON_CODE			AS 'Surgeon',
	OrganisationSites.Description													AS 'Requesting Organisation',
	pathologist.CON_DESC + ' - ' + pathologist.CON_CODE						AS 'Pathologist',
	OrganisationSites2.Description													AS 'Reporting Organisation',
	ltblDIAGNOSIS.DIAG_CODE +' - '+ ltblDIAGNOSIS.DIAG_DESC					AS 'Diagnosis Site (ICD)',
	CAST(SNOMEDCTConcepts.SNOMEDCode AS varchar) + ' - ' + SNOMEDCTConcepts.[Description] AS 'Diagnosis Site (SNOMED)',
	ltblLATERALITY.LAT_DESC													AS 'Tumour Laterality',
	ltblDERM_SPECIMEN.TYPE_DESC												AS 'Specimen Type',	
	tblPATHOLOGY_DERMATOLOGY.LESION_INDICATOR								AS 'Skin Cancer Lesion Number',
	dbo.RemoveLastCharacter
	(CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_SIZE_W IS NOT NULL THEN CAST(tblPATHOLOGY_DERMATOLOGY.R_SIZE_W as varchar(50)) + 'x' ELSE '' END +
		CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_SIZE_H IS NOT NULL THEN CAST(tblPATHOLOGY_DERMATOLOGY.R_SIZE_H as varchar(50)) + 'x' ELSE '' END +
		CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_SIZE_D IS NOT NULL THEN CAST(tblPATHOLOGY_DERMATOLOGY.R_SIZE_D as varchar(50)) + 'x' ELSE '' END)
																			AS 'Size of Specimen',	
	tblMAIN_PATHOLOGY.N8_8_INVASIVE_LESION									AS 'Max Tumour Diameter (mm)',						
	CASE
		WHEN tblPATHOLOGY_DERMATOLOGY.R_NODULE = '1'
		THEN 'Present'
		WHEN tblPATHOLOGY_DERMATOLOGY.R_NODULE = '0' 
		THEN 'Absent'
		ELSE NULL
	END																		AS 'Nodule',
	dbo.RemoveLastCharacter
			(CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_NODULE_BREADTH IS NOT NULL THEN CAST(tblPATHOLOGY_DERMATOLOGY.R_NODULE_BREADTH as varchar(50)) + 'x' ELSE '' END +
				CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_NODULE_HEIGHT IS NOT NULL THEN CAST(tblPATHOLOGY_DERMATOLOGY.R_NODULE_HEIGHT as varchar(50)) + 'x' ELSE '' END)		
																			AS 'Size of Nodule (mm)',	
	CASE
		WHEN tblPATHOLOGY_DERMATOLOGY.R_BORDER = '1'
		THEN 'Irregular'
		WHEN tblPATHOLOGY_DERMATOLOGY.R_BORDER = '0' 
		THEN 'Regular'
		ELSE NULL
	END																		AS 'Border of Lesion',	
	CASE
		WHEN tblPATHOLOGY_DERMATOLOGY.R_PIGMENTATION = '1'
		THEN 'Variable'
		WHEN tblPATHOLOGY_DERMATOLOGY.R_NODULE = '0' 
		THEN 'Uniform'
		ELSE NULL
	END																		AS 'Pigmentation of Lesion',
	tblMAIN_PATHOLOGY.N8_14_NODES											AS 'No of Nodes Examined',
	tblMAIN_PATHOLOGY.N8_15_POSITIVE_NODES									AS 'No of Positive Nodes',
	CAST(ltblSNOMedCT.CT_Concept_ID AS varchar) +' - '+ ltblSNOMedCT.CT_Description			AS 'Type (SNOMed)',
	dbo.RemoveLastCharacter
	(CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_GROWTH_NODULAR = '1' THEN 'Nodular' + ';' ELSE '' END +
		CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_GROWTH_SUPER = '1' THEN 'Superficial' + ';' ELSE '' END +
		CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_GROWTH_INFIL = '1' THEN 'Infiltrative/Morphoeic' + ';' ELSE '' END +
		CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_GROWTH_MICRO = '1' THEN 'Micronodular' + ';' ELSE '' END +
		CASE WHEN tblPATHOLOGY_DERMATOLOGY.R_GROWTH_OTHER = '1' THEN 'Other' + ';' ELSE '' END)
																			AS 'Type of Growth Pattern',
	ltblDIFFERENTIATION.GRADE_CODE +' - '+ ltblDIFFERENTIATION.GRADE_DESC	AS 'Grade of Differentiation',
	CASE tblMAIN_PATHOLOGY.N8_9_SYNC_TUMOUR
		WHEN 'Y' THEN 'Yes'
		WHEN 'N' THEN 'No'
		WHEN '9' THEN 'Not Known'
		ELSE NULL
	END																		AS 'Synchronous Tumour',
	ltblMARGINS.MARGIN_DESC													AS 'Excision Margins',
	ltblSKIN_LAT.LAT_DESC													AS 'Invasion Laterality',
	CASE tblPATHOLOGY_DERMATOLOGY.R_PERIPHERAL_MARGIN
		WHEN '1' THEN 'Involved'
		WHEN '0' THEN 'Clear'
		WHEN '9' THEN 'Uncertain'
		ELSE NULL
	END																		AS 'Peripheral Margin',
	tblPATHOLOGY_DERMATOLOGY.R_DISTANCE_PERIPHERAL							AS 'Distance to Nearest Peripheral Margin (mm)',
	CASE tblPATHOLOGY_DERMATOLOGY.R_DEEP_MARGIN
		WHEN '1' THEN 'Involved'
		WHEN '0' THEN 'Clear'
		WHEN '9' THEN 'Uncertain'
		ELSE NULL
	END																		AS 'Deep Margin',
	tblPATHOLOGY_DERMATOLOGY.R_DISTANCE_DEEP								AS 'Distance to Nearest Deep (mm)',
	ltblVASC_LYMPH_INVASION.VASC_LYMPH_DESC									AS 'Vascular/Lymphatic Invasion',
	tblPATHOLOGY_DERMATOLOGY.R_ULCERATION									AS 'Ulceration',
	braf.[Desc]																AS 'BRAF Status',
	CASE tblPATHOLOGY_DERMATOLOGY.N_SK16_PERINEURAL
		WHEN 'Y' THEN 'Yes'
		WHEN 'N' THEN 'No'
		ELSE NULL
	END																		AS 'Perineural Invasion',
	CASE tblPATHOLOGY_DERMATOLOGY.R_GROWTH_PHASE
		WHEN '1' THEN 'Yes'
		WHEN '0' THEN 'No'
		ELSE NULL
	END																		AS 'Radial Growth Phase',	
	CASE tblPATHOLOGY_DERMATOLOGY.R_VGP
		WHEN '1' THEN 'Yes'
		WHEN '0' THEN 'No'
		ELSE NULL
	END																		AS 'Vertical Growth Phase',	
	CASE tblPATHOLOGY_DERMATOLOGY.R_REGRESSION
		WHEN '1' THEN 'Yes'
		WHEN '0' THEN 'No'
		ELSE NULL
	END																		AS 'Regression',	
	CASE tblPATHOLOGY_DERMATOLOGY.R_MICROSATELLITES
		WHEN '1' THEN 'Yes'
		WHEN '0' THEN 'No'
		ELSE NULL
	END																		AS 'Microsatellites',	
	CASE tblPATHOLOGY_DERMATOLOGY.R_NAEVUS
		WHEN '1' THEN 'Yes'
		WHEN '0' THEN 'No'
		ELSE NULL
	END																		AS 'Co-existing Naevus',	
	CASE tblPATHOLOGY_DERMATOLOGY.R_DYSPLASTIC
		WHEN '1' THEN 'Yes'
		WHEN '0' THEN 'No'
		ELSE NULL
	END																		AS 'Dysplastic',	
	tblPATHOLOGY_DERMATOLOGY.R_MITOTIC_RATE									AS 'Mitotic Rate',
	
	CASE tblPATHOLOGY_DERMATOLOGY.R_LYMPHOCYTES
		WHEN '1' THEN 'Brisk'
		WHEN '2' THEN 'Non-Brisk'
		WHEN '0' THEN 'Absent'
		ELSE NULL
	END																		AS 'Infiltrating Lymphocytes',	
	tblPATHOLOGY_DERMATOLOGY.R_THICKNESS									AS 'Breslow Thickness (mm)',	
	ltblCLARK_LEVEL.LEVEL_DESC												AS 'Clarks Level',
	tblPATHOLOGY_DERMATOLOGY.L_AJCC_TEXT									AS 'AJCC Staging',		
	NULLIF('pT: ' + ISNULL(tblMAIN_PATHOLOGY.N8_16_PATH_T_STAGE, '')
	+ ISNULL(tblMAIN_PATHOLOGY.L_PATH_T_LETTER, '')
	+ ' pN: ' + ISNULL(tblMAIN_PATHOLOGY.N8_17_PATH_N_STAGE, '')
	+ ISNULL(tblMAIN_PATHOLOGY.L_PATH_N_LETTER, '')
	+ ' pM: ' + ISNULL(tblMAIN_PATHOLOGY.N8_18_PATH_M_STAGE, '')
	+ ISNULL(tblMAIN_PATHOLOGY.L_PATH_M_LETTER, ''), 'pT:  pN:  pM: ')		AS 'Staging',
	tblMAIN_PATHOLOGY.L_TNM_STAGE_GROUPING									AS 'TNM Stage Grouping (Pathological)',
	tblMAIN_PATHOLOGY.L_PATHOLOGY_TEXT										AS 'Pathology Report Text'
FROM
	tblMAIN_PATHOLOGY
	INNER JOIN tblPATHOLOGY_DERMATOLOGY
		ON tblMAIN_PATHOLOGY.PATHOLOGY_ID = tblPATHOLOGY_DERMATOLOGY.PATHOLOGY_ID
	INNER JOIN tblMAIN_REFERRALS
		ON tblMAIN_PATHOLOGY.CARE_ID = tblMAIN_REFERRALS.CARE_ID
	INNER JOIN tblDEMOGRAPHICS
		ON tblMAIN_REFERRALS.PATIENT_ID = tblDEMOGRAPHICS.PATIENT_ID
	LEFT JOIN ltblINVESTIGATION_TYPE
		ON tblMAIN_PATHOLOGY.N8_1_PATHOLOGY_TYPE = ltblINVESTIGATION_TYPE.TYPE_CODE
	LEFT JOIN ltblSPECIMEN
		ON tblMAIN_PATHOLOGY.N8_22_SPECIMEN_NATURE = ltblSPECIMEN.SPECIMEN_CODE
	LEFT JOIN ltblREPORT_STATUS
		ON tblMAIN_PATHOLOGY.N8_21_REPORT_STATUS = ltblREPORT_STATUS.STATUS_CODE	
	LEFT JOIN OrganisationSites
		ON tblMAIN_PATHOLOGY.N8_23_REQUEST_ORG = OrganisationSites.Code
	LEFT JOIN OrganisationSites OrganisationSites2
		ON tblMAIN_PATHOLOGY.N8_5_ORG_CODE = OrganisationSites2.Code
	LEFT JOIN ltblDIAGNOSIS
		ON tblMAIN_PATHOLOGY.N8_6_DIAGNOSIS = ltblDIAGNOSIS.DIAG_CODE
	LEFT JOIN ltblLATERALITY
		ON tblMAIN_PATHOLOGY.N8_7_TUMOUR_LATERALITY = ltblLATERALITY.LAT_CODE
	LEFT JOIN ltblDIFFERENTIATION
		ON tblMAIN_PATHOLOGY.N8_11_GRADE_DIFF = ltblDIFFERENTIATION.GRADE_CODE
	LEFT JOIN ltblMARGINS
		ON tblMAIN_PATHOLOGY.N8_13_EXCISION_MARGINS = ltblMARGINS.MARGIN_CODE
	LEFT JOIN ConsultantsByCode
		ON tblMAIN_PATHOLOGY.N8_24_REQUEST_BY = ConsultantsByCode.NATIONAL_CODE 
	LEFT JOIN ConsultantsByCode pathologist
		ON pathologist.NATIONAL_CODE = tblMAIN_PATHOLOGY.N8_4_PATHOLOGIST
	LEFT JOIN ltblDERM_SPECIMEN
		ON tblPATHOLOGY_DERMATOLOGY.N_SK25_SPECIMEN_TYPE = ltblDERM_SPECIMEN.TYPE_CODE
	LEFT JOIN ltblSKIN_LAT
		ON tblPATHOLOGY_DERMATOLOGY.L_INVASION_LAT = ltblSKIN_LAT.LAT_CODE
	LEFT JOIN ltblVASC_LYMPH_INVASION
		ON tblMAIN_PATHOLOGY.N8_12_CANCER_INVASION = ltblVASC_LYMPH_INVASION.VASC_LYMPH_CODE
	LEFT JOIN ltblCLARK_LEVEL
		ON tblPATHOLOGY_DERMATOLOGY.R_CLARK_LEVEL = ltblCLARK_LEVEL.LEVEL_CODE
	LEFT JOIN ltblSKIN_AJCC
		ON tblPATHOLOGY_DERMATOLOGY.L_AJCC = ltblSKIN_AJCC.AJCC_CODE
	LEFT JOIN ltblBRAFStatus braf
		ON tblPATHOLOGY_DERMATOLOGY.BRAFStatus = braf.ID
	LEFT JOIN ltblSNOMedCT 
		ON tblMAIN_PATHOLOGY.SNOMedCT = ltblSNOMedCT.CT_Snomed_ID
	LEFT JOIN tblREFERRAL_DERMATOLOGY
		ON tblMAIN_PATHOLOGY.CARE_ID = tblREFERRAL_DERMATOLOGY.CARE_ID
	LEFT JOIN SNOMEDCTConcepts 
		ON SNOMEDCTConcepts.ID = tblMAIN_PATHOLOGY.SNOMEDDiagnosisID

Columns

View Definition

Possibly Referenced Tables/Views