BIvwAOReferral

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ReferralID

int

null

Patient Name

varchar

112

null

Gender

varchar

√

null

Patient Stated Gender

varchar

√

null

NHS Number

varchar

√

null

Hospital Number

varchar

√

null

Referral To

varchar

√

null

Referral Date

varchar

√

null

Referral Time

varchar

√

null

Route of Referral

varchar

352

√

null

Referral Reason

varchar

502

√

null

Reason for Referral to Acute Oncology

varchar

2147483647

√

null

Method of Referral

varchar

302

√

null

Referrer Name

varchar

250

√

null

Referrer Phone Number

varchar

250

√

null

Referrer Email

varchar

250

√

null

Emergency Admission?

varchar

√

null

Arrival Date

date

√

null

Arrival Time

varchar

√

null

Admission Date

date

√

null

Admission Time

varchar

√

null

Patient Type

varchar

8000

√

null

Primary Tumour Site

varchar

√

null

Primary Tumour SubSite

varchar

√

null

Is MSCC suspected?

varchar

√

null

Has imaging been requested?

varchar

254

√

null

Cancer Complications:CancerProgression

varchar

√

null

Cancer Complications:Shortness Of Breath

varchar

√

null

Cancer Complications:SVCO

varchar

√

null

Cancer Complications:Ascites

varchar

√

null

Cancer Complications:PluralEffusion

varchar

√

null

Cancer Complications:PE/DVT

varchar

√

null

Cancer Complications:Pain

varchar

√

null

Cancer Complications:Other Metabolic

varchar

√

null

Cancer Complications:Hypercalcaemia

varchar

√

null

Cancer Complications:VTE

varchar

√

null

Cancer Complications:BrainMets

varchar

√

null

Cancer Complications:Tamponade

varchar

√

null

Cancer Complications:Other CNS

varchar

√

null

Cancer Complications:Other type of progression

varchar

√

null

Cancer Complications:Other Cancer Complications

varchar

255

√

null

Additional CancerComplications

varchar

2147483647

√

null

Neutropenic Sepsis suspected?

varchar

√

null

was antibiotic administered?

varchar

254

√

null

Treatment Complications:Other Sepsis

varchar

√

null

Treatment Complications:Myelosuppression

varchar

√

null

Treatment Complications:Biochemical/Metabolic

varchar

√

null

Treatment Complications:Dermatological

varchar

√

null

Treatment Complications:Diarrhoea-chemo

varchar

√

null

Treatment Complications:Diarrhoea-RT

varchar

√

null

Treatment Complications:Other RT effects

varchar

√

null

Treatment Complications:PE/DVT

varchar

√

null

Treatment Complications:Emesis

varchar

√

null

Treatment Complications:Extravasation

varchar

√

null

Treatment Complications:VTE

varchar

√

null

Other Treatment Complications

varchar

255

√

null

Additional Treatment Complications

varchar

2147483647

√

null

Current Treatment

varchar

100

√

null

Treatment Intent

varchar

√

null

Treatment Regimen

varchar

257

√

null

Consultant/Haematologist

varchar

√

null

Consultant/Haematologist (other)

varchar

250

√

null

PrimaryTreatmentCentreID

nvarchar

500

√

null

Date of Discharge from AOS

varchar

√

null

Date of Discharge from Hospital

varchar

√

null

Discharge Destination

varchar

257

√

null

Early DischargeRecomended

varchar

√

null

Admission Avoided?

varchar

√

null

Outcome:Remains Inpatient

varchar

√

null

Outcome:Active Oncology Treatment

varchar

√

null

Outcome:GP Follow Up

varchar

√

null

Outcome:PalliativeCareReferral

varchar

√

null

Outcome:No Further AO Input Planned

varchar

√

null

Outcome:Attending OPA

varchar

√

null

Outcome:Taken Over By On-call Consultant

varchar

√

null

Outcome:Cancer Referral Added

varchar

√

null

Outcome:End of Life Care

varchar

√

null

Outcome:Taken Over By Site Specific Team

varchar

√

null

Outcome:Supportive/Palliative Care

varchar

√

null

Outcome:Inappropriate Referral

varchar

√

null

other outcome

varchar

250

√

null

Discharge/Outcome Comments

varchar

2147483647

√

null

Major:New Cancer

varchar

√

null

Major:Chemo/RT Complication

varchar

√

null

varchar

√

null

Major:Cancelling Unnecessary Tests

varchar

√

null

Major:Preventing Admission

varchar

√

null

Major:Symptom Management

varchar

√

null

Major:AO CNS Present at Significant News

varchar

√

null

Major:AO CNS Present at Breaking Bad News

varchar

√

null

Major:AO CNS Gave Diagnosis to Patient

varchar

√

null

Other Major Intervention

varchar

255

√

null

Intermediate:Psychological Support (patient/carer)

varchar

√

null

Intermediate:Information (patient/carer)

varchar

√

null

Intermediate:Advised Referral to Another Team

varchar

√

null

Intermediate:Advised Referral to Another Hospital

varchar

√

null

Other Intermediate Intervention

varchar

255

√

null

Minor:Organising FU Appointment

varchar

√

null

Minor:Check Patient Progress

varchar

√

null

Minor:Inpatient FU

varchar

√

null

Other Minor Intervention

varchar

255

√

null

Patient Death Status

varchar

√

null

View Definition

CREATE VIEW [dbo].[BIvwAOReferral]
		AS
		SELECT 
-- ShowSELECT 
-- Shows referral number from tblAO_REFERRAL
tblAO_REFERRAL.ReferralID,

-- Sorts Patient name formatted as (Surname, Firstname)
ISNULL(tblDEMOGRAPHICS.N1_5_SURNAME, '') + ISNULL(', ' + tblDEMOGRAPHICS.N1_6_FORENAME, '') as [Patient Name],

-- Uses left join on Gender lookup tables to show gender and stated gender 
ltblGENDER.GENDER_DESC as [Gender], ltblPERSON_STATED_GENDER.STATED_GENDER_DESC as [Patient Stated Gender],

-- NHS Number and Hospital Number
tblDEMOGRAPHICS.N1_1_NHS_NUMBER as [NHS Number], tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER as [Hospital Number],

-- Uses left join on ltblAO_REFERRED_TO to show ReferredTo
ltblAO_REFERRED_TO.ReferredToDesc as [Referral To], 

-- Shows Date And Time
CONVERT(varchar(10), [tblAO_REFERRAL].[ReferredDate], 103) as [Referral Date], CONVERT(varchar(5), [tblAO_REFERRAL].[ReferredTime]) as [Referral Time], 

-- Shows route of referral and other notes adds other notes to the reason field seperated by a ", " also checks if other notes are null and returns only Other if they are.
ltblAO_ROUTE_OF_REFERRAL.RouteDesc + ISNULL ( ', ' + tblAO_REFERRAL.RouteOfReferralOther , '') as [Route of Referral],

-- Shows Reason for Referral and other notes adds other notes to the reason field seperated by a ", " also checks if other notes are null and returns only Other if they are.
ltblAO_REASON_FOR_REFERRAL.ReasonDesc + ISNULL ( ', ' + tblAO_REFERRAL.ReasonForReferralOther , '') as [Referral Reason] , tblAO_REFERRAL.ReasonForReferralToAcuteOncology as [Reason for Referral to Acute Oncology], 

--Shows Method of Referral and related notes
ltblAO_METHOD_OF_REFERRAL.MethodDesc + ISNULL ( ', ' + tblAO_REFERRAL.MethodOfReferralOther , '') as [Method of Referral],

-- Shows referrer Name and contact details
tblAO_REFERRAL.ReferrerName as [Referrer Name], tblAO_REFERRAL.ReferrerContactTel as [Referrer Phone Number], tblAO_REFERRAL.ReferrerContactEmail as [Referrer Email], 

-- Checks value of EmergencyAdmissionID and makes readable
CASE tblAO_REFERRAL.EmergencyAdmissionID 
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	WHEN 3 THEN 'Not Known'
	END as [Emergency Admission?],

tblAO_REFERRAL.ArrivalDate as [Arrival Date],
convert(varchar(5), tblAO_REFERRAL.ArrivalTime) as [Arrival Time],
tblAO_REFERRAL.AdmissionDate as [Admission Date],
convert(varchar(5), tblAO_REFERRAL.AdmissionTime) as [Admission Time],

dbo.RemoveLastCharacter(
		(SELECT [Description] + ';'
		FROM lnkAO_REFERRAL_PATIENT_TYPE patientTypes
		INNER JOIN tblAO_REFERRAL a
		ON patientTypes.ReferralID = a.ReferralID
		AND patientTypes.ReferralID = tblAO_REFERRAL.ReferralID
		LEFT JOIN ltblAOPatientType pt ON pt.ID =patientTypes.PatientTypeID
		ORDER BY pt.COSDValue
		FOR XML PATH('')
		)) AS [Patient Type],

--uses lookup table to find and display cancer site and cancer sub site
ltblCANCER_SITES.CA_SITE as [Primary Tumour Site], ltblCANCER_SUB_SITE.SUB_DESC as [Primary Tumour SubSite], 

-- Shows if MSCC is suspected and realted info
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsMsccSuspected 
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Is MSCC suspected?],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsImagingRequested
	WHEN 0 THEN 'No' + ISNULL(', ' + tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsImagingNotRequestedReason , '')
	WHEN 1 THEN 'Yes'
	WHEN 2 THEN 'Not Known'
	END as [Has imaging been requested?],
	
--Shows Cancer Complications
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsCancerProgression
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:CancerProgression],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsShortnessOfBreath
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Shortness Of Breath],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsSvco 
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:SVCO],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsAscites
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Ascites],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsPluralEffusion
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:PluralEffusion],

CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsPeDvt
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:PE/DVT],

CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsPain
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Pain],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsOtherMetabolic
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Other Metabolic],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsHypercalcaemia
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Hypercalcaemia],
		
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsVte
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:VTE],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsBrainMets
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:BrainMets],

CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsTamponade
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Tamponade],
	
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsOtherCns
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Other CNS],
		
CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsOtherTypeProgression
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Cancer Complications:Other type of progression],

CASE tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsOtherDesc, '') 
	END as [Cancer Complications:Other Cancer Complications],
		
tblAO_COMPLICATIONS_TREATMENT.CancerComplicationsAdditionalComplications as [Additional CancerComplications], 

--shows if Neutropenic Sepsis is suspected and related info
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsNeutropenicSepsisSuspected
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Neutropenic Sepsis suspected?],

CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsAnitibioticAdministered
	WHEN 0 THEN 'No, ' + tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsAnitibioticNotAdministeredReason
	WHEN 1 THEN 'Yes'
	END as [was antibiotic administered?],

--Shows Treatment Complications
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsOtherSepsis
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Other Sepsis],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsMyelosuppression
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Myelosuppression],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsBiochemicalMetabolic
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Biochemical/Metabolic],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsDermatological
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Dermatological],
		
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsDiarrhoeaChemo
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Diarrhoea-chemo],
			
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsDiarrhoeaRt
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Diarrhoea-RT],
				
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsOtherRtEffects
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Other RT effects],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsPeDvt
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:PE/DVT],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsEmesis
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Emesis],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsExtravasation
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:Extravasation],

CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsVte
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Treatment Complications:VTE],
	
CASE tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsOtherDesc , '')
	END as [Other Treatment Complications],

tblAO_COMPLICATIONS_TREATMENT.TreatmentComplicationsAdditionalComplications as [Additional Treatment Complications], 

--shows treatement, intent and Regimen information
ltblDEFINITIVE_TYPE.TREAT_DESC as [Current Treatment], 

--Shows Treatment Intent from Table (note: there was an extra table called ltblAO_TREATMENT_INTENT which could easily be mistaken for the lookup table)
ti.NonSurgicalIntentDesc as [Treatment Intent],

--shows treatment regimen from linked lookup table and adds note from "other" if USED formatted as (Other, NOTES) ISNULL is used to ensure that the other option is still displayed if no notes were provided.
CASE ltblAO_TREATMENT_REGIMEN.RegimenDesc 
	WHEN 'Other' THEN 'Other' + ISNULL(', ' + tblAO_COMPLICATIONS_TREATMENT.TreatmentRegimenOther, '')
	ELSE ltblAO_TREATMENT_REGIMEN.RegimenDesc 
	END as [Treatment Regimen],

--uses lookup table to display Consultant/Haematologist
ltblCONSULTANTS.CON_DESC as [Consultant/Haematologist], 

--displays notes in Other Consultant Heamatologist field
tblAO_COMPLICATIONS_TREATMENT.OtherConsultantHaematologist as [Consultant/Haematologist (other)],

--shows treatment centre useing the ORG_CODES lookup table
OrganisationSites.[Description] as [PrimaryTreatmentCentreID],

--shows date of discharge from AOS
CONVERT(varchar(10), [tblAO_OUTCOME].[DateDischargedAOS], 103) as [Date of Discharge from AOS],

--shows date of discharge from hospital 
CONVERT(varchar(10), [tblAO_OUTCOME].[DateDischargedHospital], 103) as [Date of Discharge from Hospital],
 
--shows discharge destination info from lookup table
CASE ltblAO_DISCHARGE_DESTINATION.DischargeDestinationDesc 
	WHEN 'Other' THEN 'Other' + ISNULL(', ' + tblAO_OUTCOME.DischargeDestinationOther, '')
	ELSE ltblAO_DISCHARGE_DESTINATION.DischargeDestinationDesc 
	END	AS [Discharge Destination], 

--displays if early discharge is recomended
CASE tblAO_OUTCOME.EarlyDischargeRecommended
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Early DischargeRecomended], 
	
--displays if admission was avoided
CASE tblAO_OUTCOME.AdmissionAvoided
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Admission Avoided?], 

--shows what Outcome check boxes are checked and shows them in english (ticked is yes and not ticked is no)
CASE tblAO_OUTCOME.RemainsInpatient
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Remains Inpatient],
	
CASE tblAO_OUTCOME.ActiveOncologyTreatment
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Active Oncology Treatment],
	
CASE tblAO_OUTCOME.GPFollowUp
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:GP Follow Up],
	
CASE tblAO_OUTCOME.PalliativeCareReferral 
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + CONVERT(varchar(10), [tblAO_OUTCOME].[PalliativeCareReferralDate], 103), '')
	END AS [Outcome:PalliativeCareReferral],
	
CASE tblAO_OUTCOME.NoFurtherAOInput
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:No Further AO Input Planned],
	
CASE tblAO_OUTCOME.AttendingOPA
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Attending OPA],
	
CASE tblAO_OUTCOME.TakenOverByConsultant
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Taken Over By On-call Consultant],
	
CASE tblAO_OUTCOME.AddReferralToSCR
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Cancer Referral Added],
	
CASE tblAO_OUTCOME.EndOfLifeCare
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:End of Life Care],
	
CASE tblAO_OUTCOME.TakenOverByTeam
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Taken Over By Site Specific Team],
	
CASE tblAO_OUTCOME.SupportPalliativeCare
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Supportive/Palliative Care],
	
CASE tblAO_OUTCOME.InappropriateReferral
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Outcome:Inappropriate Referral],
	
tblAO_OUTCOME.OtherOutcome AS [other outcome],

--shows the contents of the Discharge/Outcome Comments from the patient discharge & outcome tab of Outcome
tblAO_OUTCOME.DischargeOutcomeComments as [Discharge/Outcome Comments],

--shows what Major Intervention check boxes are checked and shows them in english (ticked is yes and not ticked is no)
CASE tblAO_OUTCOME.MajorNewCancer
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:New Cancer],
	
CASE tblAO_OUTCOME.MajorChemoRTComplication
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:Chemo/RT Complication],
	
CASE tblAO_OUTCOME.MajorRecommendDiagnosticTests
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:Recommend Diagnostic Tests],
	
CASE tblAO_OUTCOME.MajorCancellingUnnecessaryTests
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:Cancelling Unnecessary Tests],
	
CASE tblAO_OUTCOME.MajorPreventingAdmission
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:Preventing Admission],
	
CASE tblAO_OUTCOME.MajorSymptomManagement
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:Symptom Management],
	
CASE tblAO_OUTCOME.MajorCNSPresentSignificantNews
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:AO CNS Present at Significant News],
	
CASE tblAO_OUTCOME.MajorCNSPresentBadNews
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:AO CNS Present at Breaking Bad News],
	
CASE tblAO_OUTCOME.MajorCNSGaveDiagnosis
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Major:AO CNS Gave Diagnosis to Patient],
	
CASE tblAO_OUTCOME.MajorOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_OUTCOME.MajorOtherIntervention, '')
	END AS [Other Major Intervention],	
 
--shows what Intermediate Intervention check boxes are checked and shows them in english (ticked is yes and not ticked is no)
CASE tblAO_OUTCOME.IntermediatePsychologicalSupport
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Intermediate:Psychological Support (patient/carer)],	
 
CASE tblAO_OUTCOME.IntermediateInformation
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Intermediate:Information (patient/carer)],	
 
CASE tblAO_OUTCOME.IntermediateAdvisedReferralAnotherTeam
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Intermediate:Advised Referral to Another Team],	
 
CASE tblAO_OUTCOME.IntermediateAdvisedReferralOtherHospital
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Intermediate:Advised Referral to Another Hospital],	
 
CASE tblAO_OUTCOME.IntermediateOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_OUTCOME.IntermediateOtherIntervention, '')
	END AS [Other Intermediate Intervention],	

--shows what Minor Intervention check boxes are checked and shows them in english (ticked is yes and not ticked is no)

CASE tblAO_OUTCOME.MinorOrganisingFUAppointment
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Minor:Organising FU Appointment],

CASE tblAO_OUTCOME.MinorCheckPatientProgress
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Minor:Check Patient Progress],

CASE tblAO_OUTCOME.MinorInpatientFU
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Minor:Inpatient FU],
	
CASE tblAO_OUTCOME.MinorOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_OUTCOME.MinorOtherIntervention, '')
	END AS [Other Minor Intervention],	

--checks the Death Stutus of a patient and returns alive if alive or the date of death if dead
CASE tblDEMOGRAPHICS.L_DEATH_STATUS
	WHEN 0 THEN 'Alive'
	WHEN 1 THEN 'Died on ' + CONVERT(varchar(10), [tblDEMOGRAPHICS].[N15_1_DATE_DEATH], 103)
	END AS [Patient Death Status] 

FROM tblAO_REFERRAL

-- MAIN TABLE JOINS
LEFT JOIN tblDEMOGRAPHICS on tblAO_REFERRAL.PatientID = tblDEMOGRAPHICS.PATIENT_ID
LEFT JOIN tblAO_COMPLICATIONS_TREATMENT on tblAO_COMPLICATIONS_TREATMENT.ReferralID = tblAO_REFERRAL.ReferralID
LEFT JOIN tblAO_OUTCOME on tblAO_REFERRAL.ReferralID = tblAO_OUTCOME.ReferralID

-- Joins Gender Lookup Tables
LEFT JOIN ltblGENDER ON tblDEMOGRAPHICS.N1_9_SEX = ltblGENDER.GENDER_CODE
LEFT JOIN ltblPERSON_STATED_GENDER ON tblDEMOGRAPHICS.STATED_GENDER_CODE = ltblPERSON_STATED_GENDER.STATED_GENDER_CODE

-- Joins Refered to lookup table
LEFT JOIN ltblAO_REFERRED_TO on tblAO_REFERRAL.ReferredToID = ltblAO_REFERRED_TO.ReferredToID

-- Joins Refered Route lookup table
LEFT JOIN ltblAO_ROUTE_OF_REFERRAL on tblAO_REFERRAL.RouteOfReferralID = ltblAO_ROUTE_OF_REFERRAL.RouteID

-- Joins REASON_FOR_REFERRAL lookup table
LEFT JOIN ltblAO_REASON_FOR_REFERRAL on tblAO_REFERRAL.ReasonForReferralID = ltblAO_REASON_FOR_REFERRAL.ReasonID

-- Joins METHOD_OF_REFERRAL lookup table
LEFT JOIN ltblAO_METHOD_OF_REFERRAL on tblAO_REFERRAL.MethodOfReferralID = ltblAO_METHOD_OF_REFERRAL.MethodID

-- joins CancerSite and CancerSubSite tables
LEFT JOIN ltblCANCER_SITES ON tblAO_REFERRAL.PrimaryTumourSiteID = ltblCANCER_SITES.CA_ID
LEFT JOIN ltblCANCER_SUB_SITE ON tblAO_REFERRAL.PrimaryTumourSubSiteID = ltblCANCER_SUB_SITE.SUB_ID

-- Join ltblDEFINITIVE_TYPE for current treatment
LEFT JOIN ltblDEFINITIVE_TYPE ON tblAO_COMPLICATIONS_TREATMENT.CurrentTreatmentID = ltblDEFINITIVE_TYPE.TREAT_CODE

--joins treatment intent table (note: there was an extra table called ltblAO_TREATMENT_INTENT which could easily be mistaken for the lookup table)
LEFT JOIN ltblTreatmentIntent ti ON tblAO_COMPLICATIONS_TREATMENT.CurrentTreatmentIntentID = ti.IntentID

--joins treatment regimen table
LEFT JOIN ltblAO_TREATMENT_REGIMEN ON tblAO_COMPLICATIONS_TREATMENT.TreatmentRegimenID = ltblAO_TREATMENT_REGIMEN.RegimenID

--joins consultants table
LEFT JOIN ltblCONSULTANTS on tblAO_COMPLICATIONS_TREATMENT.OncologistHaematologistID = ltblCONSULTANTS.CON_ID

--joins Discharge destination table
LEFT JOIN ltblAO_DISCHARGE_DESTINATION ON tblAO_OUTCOME.DischargeDestinationID = ltblAO_DISCHARGE_DESTINATION.DischargeDestinationID

--joins org codes table to obtain org desc
LEFT JOIN OrganisationSites ON tblAO_COMPLICATIONS_TREATMENT.PrimaryTreatmentCentreID = OrganisationSites.ID

Columns

View Definition

Possibly Referenced Tables/Views