vwNBOCAP_FULL_SURGERIES

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

EVENT_ID

int

√

null

CARE_ID

int

null

NHSNumber

varchar

√

null

DateOfBirth

date

√

null

ProviderOrganisation

varchar

√

null

ASAGrade

int

√

null

CancerTreatmentCurability

varchar

√

null

CPEXAnaerobicThreshold

real

√

null

BMI

float

√

null

DateOfSurgery

smalldatetime

√

null

SurgicalUrgencyModeOfOperation

varchar

null

Consultant

varchar

√

null

PrimaryProcedure

varchar

√

null

SurgicalAccess

varchar

√

null

ImmediatePostoperativeCare

varchar

√

null

View Definition

-- Created by Paul Stevens, February 2014
-- Amended by Paul Stevens, June 2014

CREATE VIEW [dbo].[vwNBOCAP_FULL_SURGERIES]
AS
SELECT		nbs.EVENT_ID,
			nbs.CARE_ID,
			CASE
			WHEN dbo.IsNSTSValid(d.PATIENT_ID) = 1
			THEN d.N1_1_NHS_NUMBER 
			ELSE NULL
			END NHSNumber,
			CASE
			WHEN (d.N1_10_DATE_BIRTH IS NOT NULL AND YEAR(d.N1_10_DATE_BIRTH) >= 1900) 
			THEN d.N1_10_DATE_BIRTH
			ELSE NULL
			END DateOfBirth,
			CASE
			WHEN LEN(ms.N7_1_SITE_CODE) = 5
			THEN ms.N7_1_SITE_CODE
			ELSE NULL
			END ProviderOrganisation,
			CASE
			WHEN ms.ASAGrade IN ('1','2','3','4','5','6')
			THEN ms.ASAGrade
			ELSE '99'
			END ASAGrade,
			CASE
			WHEN NULLIF(sc.L_OUTCOME,'') = 'Curative'
			THEN 'C'
			WHEN NULLIF(sc.L_OUTCOME,'') = 'Palliative'
			THEN 'P'
			WHEN NULLIF(sc.L_OUTCOME,'') = 'Uncertain'
			THEN 'U'
			ELSE NULL 
			END CancerTreatmentCurability,			
			sc.CPEX_ASSESS AS CPEXAnaerobicThreshold,			
			ROUND([is].L_WEIGHT / 
			([is].L_HEIGHT * [is].L_HEIGHT), 1) AS BMI,
			CASE
			WHEN ms.N7_9_SURGERY_DATE >= '01-JAN-00' AND
			ms.N7_9_SURGERY_DATE <= GETDATE()
			THEN ms.N7_9_SURGERY_DATE
			ELSE NULL			
			END DateOfSurgery,									
			CASE
			WHEN (UPPER(ms.L_URGENCY) = 'ELECTIVE') THEN '01'
			WHEN (UPPER(ms.L_URGENCY) = 'SCHEDULED') THEN '02'
			WHEN (UPPER(ms.L_URGENCY) = 'URGENT') THEN '03'
			WHEN (UPPER(ms.L_URGENCY) = 'EMERGENCY') THEN '04'
			ELSE '99'
			END SurgicalUrgencyModeOfOperation,	
			CASE
			WHEN ISNUMERIC(SUBSTRING(ms.N7_2_CONSULTANT,2,7)) = 1
			THEN ms.N7_2_CONSULTANT 
			ELSE NULL
			END Consultant,		
			CASE	
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('25','26','27','28','29','39','40','251','321','410','407', '460')
					THEN 'H07.9'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('23','24','254','259','322','409','414','424','425','426','433','437', '461', '462')
					THEN 'H06.9'
				WHEN ISNULL(ms.L_PROCEDURE, '') = '406'
					THEN 'H05.9'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('32','33','34','35','36','37','336','337','338','339','340','434','435', '455')
					THEN 'H05.1'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('19','264')
					THEN 'H04.1'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('16','17','20','395','422','423')
					THEN 'H04.2'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('120','242','398','399','439','440','441','442','443')
					THEN 'H20.1'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('121','265', '281','400', '401','402','403','404','445', '465')
					THEN 'H23.9'			
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('52','126','263','394','420','421')
					THEN 'G74.9'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('46','49','59','123','124','125','127','128','276','416','417','438')			
					THEN 'H15.9'														
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('38','44','396','411','427','428','429','430', '463')
					THEN 'H08.9'						
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('18','41','258','260','275','278','397','412','431','432')
					THEN 'H09.9'							
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('30','31','43','256','257','326','331','413','452','453','454')			
					THEN 'H10.9'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('14', '252')
					THEN 'H33.5'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('1','284','285','286')
					THEN 'H33.1'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('2','3','4','5','6','7','8','9','21','22','42','436','448','449','458','459','464')
					THEN 'H33.4'
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('74', '277','450','451')
					THEN 'H41.2'
				WHEN ISNULL(ms.L_PROCEDURE, '') = '99'
					THEN 'H41.9'						
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('117','343','444','446','447')
					THEN 'H24.3'			
				WHEN ISNULL(ms.L_PROCEDURE, '') IN ('266','267','268','456','457')
					THEN 'X14'			
				WHEN ISNULL(ms.L_PROCEDURE, '') <> ''
					THEN '98'
				ELSE NULL
			END PrimaryProcedure,
			CASE 
			WHEN (ISNULL(ms.L_PROCEDURE, '') IN ('25','26','27','28','29','39','40','321','23','24',
												 '259','322','406','32','33','34','35','36','37',
												 '19','264','16','17','20','21','22','395','52','126',
												 '263','394','46','49','59','123','124','125','127',
												 '128','276','44','396','18','41','258','260','275','278',
												 '397','30','31','43','256','257','326','14','252',
												 '1','284','285','286','2','3','4','5','6','7','8',
												 '9','42','266','267','268', '461', '462', '460', '463',
												 '465', '464'))
			THEN					 
				CASE
				WHEN (sc.L_LAPAROSCOPIC = 'Open')
				THEN '1'
				WHEN (sc.L_LAP_OPEN IN ('Laparoscopy converted to open','Laparoscopy then open'))
				THEN '3'
				WHEN (sc.L_LAP_OPEN = 'Laparoscopic completed')
				THEN '4'
				WHEN (sc.L_LAP_OPEN = 'Not Applicable')
				THEN 'N'
				ELSE NULL
				END			
			END SurgicalAccess,				
			CASE
			WHEN (ms.L_TRANSFER = 'Ward')
			THEN '1'
			WHEN (ms.L_TRANSFER = 'High Care Area')
			THEN '2'
			WHEN (ms.L_TRANSFER = 'HDU')
			THEN '3'
			WHEN (ms.L_TRANSFER = 'ITU')
			THEN '4'
			ELSE NULL
			END ImmediatePostoperativeCare			
FROM		vwNBOCAP_BASE_SURGERIES nbs LEFT JOIN tblMAIN_SURGERY ms
			ON nbs.CARE_ID = ms.CARE_ID AND
			nbs.EVENT_ID = ms.SURGERY_ID
			LEFT JOIN tblSURGERY_COLORECTAL sc
			ON nbs.EVENT_ID = sc.SURGERY_ID
			LEFT JOIN tblINITIAL_ASSESSMENT [is]		
			ON ms.CARE_ID = [is].CARE_ID	
			LEFT JOIN tblMAIN_REFERRALS mr
			ON nbs.CARE_ID = mr.CARE_ID
			LEFT JOIN tblDEMOGRAPHICS d
			ON mr.PATIENT_ID = d.PATIENT_ID
			LEFT JOIN vwNBOCAP_FULL_TUMOURS nft
			ON nbs.CARE_ID = nft.CARE_ID
	WHERE	nft.OrganisationFirstSeen IS NOT NULL AND
			nft.DateOfDiagnosis IS NOT NULL AND
			nft.SourceOfReferral IS NOT NULL AND			
			nft.PerformanceStatus IS NOT NULL AND			
			--((nft.CarePlanIntent IN ('C','Z') AND nft.PlannedCancerTreatmentType IS NOT NULL) OR			
			--(nft.CarePlanIntent = 'X' AND nft.NoCancerTreatmentReason IS NOT NULL)) AND
			((nft.CarePlanIntent IN ('C','Z') AND nft.PlannedCancerTreatmentType IS NOT NULL) OR			
			(nft.CarePlanIntent = 'X' AND nft.NoCancerTreatmentReason IS NOT NULL) OR
			nft.CarePlanIntent IS NOT NULL) AND 
			nft.TCategory IS NOT NULL AND
			nft.NCategory IS NOT NULL AND
			nft.MCategory IS NOT NULL
			AND (dbo.IsNSTSValid(mr.PATIENT_ID)) = 1
			AND d.N1_10_DATE_BIRTH IS NOT NULL
			AND NULLIF(d.N1_8_POSTCODE,' ') IS NOT NULL
			AND d.N1_9_SEX IN (0,1,2,9)

Columns

View Definition

Possibly Referenced Tables/Views