BIvwHeadandNeckPathology

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

Care Professional

varchar

√

null

Requesting Organisation

nvarchar

500

√

null

Reporting Organisation

nvarchar

500

√

null

Pathologist

varchar

√

null

Diagnosis Site (ICD)

varchar

203

√

null

Diagnosis Site (SNOMED)

nvarchar

566

√

null

Clinical TNM Stage

varchar

8000

√

null

Topography (SNOMed)

varchar

√

null

Tumour Laterality

varchar

√

null

Previous Radiotherapy

varchar

√

null

Type of Resection

varchar

√

null

Previous Chemotherapy

varchar

√

null

Max Tumour Diameter (mm)

real

√

null

Distance from Invasive Tumour to Muscosal Margins (mm)

real

√

null

Max Depth of Invasion (mm)

real

√

null

Distance from Invasive Tumour to Deep Margins (mm)

real

√

null

Type (SNOMed)

varchar

133

√

null

Synchronous Tumour

varchar

√

null

HPV Status Testing Complete

varchar

√

null

HPV Test

varchar

√

null

HPV Status

varchar

√

null

Grade of Differentiation

varchar

105

√

null

Vascular/Lymphatic Invasion

varchar

√

null

Perineural Invasion

varchar

√

null

Nerve Invasion

varchar

√

null

Extrathyroid Invasion

varchar

√

null

Margins (Microscopic Resection)

varchar

√

null

Severe Dysplasia Present

varchar

√

null

Invasive Front

varchar

√

null

Severe Dysplasia at Margins

varchar

√

null

Neck Dissection Laterality (1)

varchar

√

null

Comprehensive/Selective (1)

varchar

√

null

Node Levels Present (1)

varchar

8000

√

null

Largest Metastasis (1)

real

√

null

Extracapsular Spread (1)

varchar

√

null

Levels with ECS (1)

varchar

8000

√

null

Neck Dissection Laterality (2)

varchar

√

null

Comprehensive/Selective (2)

varchar

√

null

Node Levels Present (2)

varchar

8000

√

null

Largest Metastasis (2)

real

√

null

Extracapsular Spread (2)

varchar

√

null

Levels with ECS (2)

varchar

8000

√

null

No of Nodes Examined

int

√

null

No of Positive Nodes

int

√

null

Excision Margins

varchar

100

√

null

Complete Resection at all Margins

varchar

√

null

Staging

varchar

√

null

TNM Stage Grouping (Pathological)

varchar

√

null

INSS Stage

varchar

√

null

Comments

text

2147483647

√

null

DAHNO First Tissue Biopsy

varchar

null

View Definition

CREATE VIEW [dbo].[BIvwHeadandNeckPathology]
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 'Care Professional',
	OrganisationSites.Description													AS 'Requesting Organisation',
	OrganisationSites2.Description													AS 'Reporting Organisation',
	ltblCONSULTANTS2.CON_DESC +' - '+ ltblCONSULTANTS2.CON_CODE				AS 'Pathologist',	
	ltblDIAGNOSIS.DIAG_CODE +' - '+ ltblDIAGNOSIS.DIAG_DESC					AS 'Diagnosis Site (ICD)',
	CAST(SNOMEDCTConcepts.SNOMEDCode AS varchar) + ' - ' + SNOMEDCTConcepts.[Description] AS 'Diagnosis Site (SNOMED)',																		
	CASE 
		WHEN NULLIF('T' + ISNULL(tblMAIN_REFERRALS.ClinicalTStage, '')
			+ ISNULL(tblMAIN_REFERRALS.ClinicalTLetter, '')
			+ 'N' + ISNULL(tblMAIN_REFERRALS.ClinicalNStage, '')
			+ ISNULL(tblMAIN_REFERRALS.ClinicalNLetter, '')
			+ 'M' + ISNULL(tblMAIN_REFERRALS.ClinicalMStage, ''),'') <> 'T N M'
		THEN REPLACE(NULLIF('T' + ISNULL(tblMAIN_REFERRALS.ClinicalTStage, '')
			+ ISNULL(tblMAIN_REFERRALS.ClinicalTLetter, '')
			+ 'N' + ISNULL(tblMAIN_REFERRALS.ClinicalNStage, '')
			+ ISNULL(tblMAIN_REFERRALS.ClinicalNLetter, '')
			+ 'M' + ISNULL(tblMAIN_REFERRALS.ClinicalMStage, ''),''),' ','')
			ELSE NULL
		END																	AS 'Clinical TNM Stage',
	tblMAIN_PATHOLOGY.L_PATHOLOGY_TOPOGRAPHY								AS 'Topography (SNOMed)',		
	ltblLATERALITY.LAT_DESC													AS 'Tumour Laterality',	
	tblPATHOLOGY_HEAD_NECK.R_PRE_RAD										AS 'Previous Radiotherapy',
	ltblHN_RESECTIONS.TYPE_DESC												AS 'Type of Resection',
	tblPATHOLOGY_HEAD_NECK.R_PRE_CHEMO										AS 'Previous Chemotherapy',
	tblMAIN_PATHOLOGY.N8_8_INVASIVE_LESION									AS 'Max Tumour Diameter (mm)',	
	tblPATHOLOGY_HEAD_NECK.R_MUSCOSAL										AS 'Distance from Invasive Tumour to Muscosal Margins (mm)',
	tblPATHOLOGY_HEAD_NECK.R_DEPTH											AS 'Max Depth of Invasion (mm)',
	tblPATHOLOGY_HEAD_NECK.R_DEEP											AS 'Distance from Invasive Tumour to Deep Margins (mm)',	
	CAST(ltblSNOMedCT.CT_Concept_ID AS varchar) +' - '+ ltblSNOMedCT.CT_Description			AS 'Type (SNOMed)',
	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',	
	CASE tblPATHOLOGY_HEAD_NECK.HPV_TEST_COMPLETE
		WHEN 'Y' THEN 'Yes'
		WHEN 'N' THEN 'No'
		WHEN '8' THEN 'Not applicable'
		WHEN '9' THEN 'Not known'
		ELSE NULL
	END																		AS 'HPV Status Testing Complete',
	CASE tblPATHOLOGY_HEAD_NECK.HPV_TEST
		WHEN '1' THEN 'p16 immunohistochemistry only'
		WHEN '2' THEN 'p16immunohistochemistry + High-risk HPV in situ hybridisation)'
		ELSE NULL
	END																		AS 'HPV Test',
	CASE tblPATHOLOGY_HEAD_NECK.HPV_STATUS
		WHEN '1' THEN 'Positive'
		WHEN '2' THEN 'Negative'
		ELSE NULL
	END																		AS 'HPV Status',	
	ltblDIFFERENTIATION.GRADE_CODE +' - '+ ltblDIFFERENTIATION.GRADE_DESC	AS 'Grade of Differentiation',
	CASE tblMAIN_PATHOLOGY.N8_12_CANCER_INVASION
		WHEN 'Y' THEN 'Yes'
		WHEN 'N' THEN 'No'
		WHEN 'U' THEN 'Unknown'
		WHEN '9' THEN 'Not Known'
		ELSE NULL
	END																		AS 'Vascular/Lymphatic Invasion',	
	tblPATHOLOGY_HEAD_NECK.R_PERINEURAL_INV									AS 'Perineural Invasion',
	tblPATHOLOGY_HEAD_NECK.R_NERVE											AS 'Nerve Invasion',
	tblPATHOLOGY_HEAD_NECK.R_THYROID										AS 'Extrathyroid Invasion',
	tblPATHOLOGY_HEAD_NECK.R_MARGINS										AS 'Margins (Microscopic Resection)',
	tblPATHOLOGY_HEAD_NECK.R_DYS_P											AS 'Severe Dysplasia Present',
	tblPATHOLOGY_HEAD_NECK.R_INVASIVE_FRONT									AS 'Invasive Front',
	tblPATHOLOGY_HEAD_NECK.R_DYS_M											AS 'Severe Dysplasia at Margins',	
	tblPATHOLOGY_HEAD_NECK.R_NECK											AS 'Neck Dissection Laterality (1)',																		
	tblPATHOLOGY_HEAD_NECK.R_TIVE											AS 'Comprehensive/Selective (1)',
	dbo.RemoveLastCharacter
	(CASE WHEN tblPATHOLOGY_HEAD_NECK.R_1 = '1' THEN '1;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_2 = '1' THEN '2;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_3 = '1' THEN '3;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_4 = '1' THEN '4;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_5 = '1' THEN '5;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_6 = '1' THEN '6;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_OTHER = '1' THEN 'Other;' ELSE '' END)
																			AS 'Node Levels Present (1)',
	tblPATHOLOGY_HEAD_NECK.R_LARGEST_MET									AS 'Largest Metastasis (1)',
	tblPATHOLOGY_HEAD_NECK.R_LYMPH_SPREAD									AS 'Extracapsular Spread (1)',
	dbo.RemoveLastCharacter
	(CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E1 = '1' THEN '1;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E2 = '1' THEN '2;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E3 = '1' THEN '3;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E4 = '1' THEN '4;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E5 = '1' THEN '5;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E6 = '1' THEN '6;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E_OTHER = '1' THEN 'Other;' ELSE '' END)	
																			AS 'Levels with ECS (1)',																				
	tblPATHOLOGY_HEAD_NECK.R_NECK_2											AS 'Neck Dissection Laterality (2)',
	tblPATHOLOGY_HEAD_NECK.R_TIVE_2											AS 'Comprehensive/Selective (2)',
	dbo.RemoveLastCharacter
	(CASE WHEN tblPATHOLOGY_HEAD_NECK.R_1_2 = '1' THEN '1;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_2_2 = '1' THEN '2;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_3_2 = '1' THEN '3;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_4_2 = '1' THEN '4;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_5_2 = '1' THEN '5;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_6_2 = '1' THEN '6;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_OTHER_2 = '1' THEN 'Other;' ELSE '' END)
																			AS 'Node Levels Present (2)',
	tblPATHOLOGY_HEAD_NECK.R_LARGEST_MET_2									AS 'Largest Metastasis (2)',
	tblPATHOLOGY_HEAD_NECK.R_LYMPH_SPREAD_2									AS 'Extracapsular Spread (2)',
	dbo.RemoveLastCharacter
	(CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E1_2 = '1' THEN '1;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E2_2 = '1' THEN '2;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E3_2 = '1' THEN '3;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E4_2 = '1' THEN '4;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E5_2 = '1' THEN '5;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E6_2 = '1' THEN '6;' ELSE '' END +
		CASE WHEN tblPATHOLOGY_HEAD_NECK.R_E_OTHER_2 = '1' THEN 'Other;' ELSE '' END)
																			AS 'Levels with ECS (2)',
	tblMAIN_PATHOLOGY.N8_14_NODES											AS 'No of Nodes Examined',
	tblMAIN_PATHOLOGY.N8_15_POSITIVE_NODES									AS 'No of Positive Nodes',
	ltblMARGINS.MARGIN_DESC													AS 'Excision Margins',
	tblPATHOLOGY_HEAD_NECK.R_RESECTION_MAR									AS 'Complete Resection at all Margins',
	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)',
	ltblPAED_INSS.INSS_DESC													AS 'INSS Stage',
	tblMAIN_PATHOLOGY.L_COMMENTS											AS 'Comments',
	CASE tblPATHOLOGY_HEAD_NECK.DAHNO_FIRST_TISSUE_BIOPSY
		WHEN '1' 	
		THEN 'Yes'
		ELSE 'No'
	END																		AS 'DAHNO First Tissue Biopsy'
FROM
	tblMAIN_PATHOLOGY
	INNER JOIN tblPATHOLOGY_HEAD_NECK
		ON tblMAIN_PATHOLOGY.PATHOLOGY_ID = tblPATHOLOGY_HEAD_NECK.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 tblREFERRAL_HEAD_NECK
		ON tblMAIN_REFERRALS.CARE_ID = tblREFERRAL_HEAD_NECK.CARE_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 
		AND ConsultantsByCode.IS_DELETED <> 1
	LEFT JOIN ConsultantsByCode ltblCONSULTANTS2
		ON tblMAIN_PATHOLOGY.N8_4_PATHOLOGIST = ltblCONSULTANTS2.NATIONAL_CODE
		AND ltblCONSULTANTS2.IS_DELETED <> 1
	LEFT JOIN ltblHN_RESECTIONS
		ON tblPATHOLOGY_HEAD_NECK.R_RESECTION = ltblHN_RESECTIONS.TYPE_CODE
	LEFT JOIN ltblPAED_INSS
		ON tblPATHOLOGY_HEAD_NECK.INSS_ID = ltblPAED_INSS.INSS_ID
	LEFT JOIN ltblSNOMedCT 
		ON tblMAIN_PATHOLOGY.SNOMedCT = ltblSNOMedCT.CT_Snomed_ID
	LEFT JOIN SNOMEDCTConcepts 
		ON SNOMEDCTConcepts.ID = tblMAIN_PATHOLOGY.SNOMEDDiagnosisID

Columns

View Definition

Possibly Referenced Tables/Views