BIvwAOMSCC

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

Assessment Date

varchar

√

null

Assessment Time

varchar

√

null

Assessed By CNS

nvarchar

1073741823

√

null

Assessed By Consultant

nvarchar

1073741823

√

null

Name of Oncologist/Haematologist

varchar

√

null

Record of Discussions with Treatment Team(s)

varchar

2147483647

√

null

Steroids Started?

varchar

√

null

Date Steroids Started

datetime

16,3

√

null

Type of Steroids Administered

varchar

250

√

null

Nursed Flat Prior to Referral

varchar

√

null

Imaging Requested:CT

varchar

√

null

Imaging Requested:MRI

varchar

√

null

Imaging Requested:PET

varchar

√

null

Imaging Requested:Other

varchar

255

√

null

Imaging Requested Date

varchar

√

null

Imaging Requested Time

varchar

√

null

Imaging Performed Date

varchar

√

null

Imaging Performed Time

varchar

√

null

Time Lapse (Requested to Performed)

int

√

null

Imaging Reported Date

varchar

√

null

Imaging Reported Time

varchar

√

null

Time Lapse (Performed to Reported)

int

√

null

Reporting Organisation

nvarchar

500

√

null

MSCC Confirmed?

varchar

√

null

Date Spinal Surgeons Informed

varchar

√

null

Date Radiotherapy Informed

varchar

√

null

Diagnosis Delay Reasons

varchar

2147483647

√

null

Primary Treatment Type:Surgery

varchar

√

null

Primary Treatment Type:Radiotherapy

varchar

√

null

Primary Treatment Type:Chemotherapy

varchar

√

null

Primary Treatment Type:Best Supportive Care

varchar

√

null

Primary Treatment Type:Other

varchar

255

√

null

Treatment Start Date

varchar

√

null

Treatment Start Time

varchar

√

null

Time Lapse (Imaging Request To Treatment Start)

int

√

null

Time Lapse (Imaging Performed To Treatment Start)

int

√

null

Primary Treatment Organisation

nvarchar

500

√

null

PTDelayReasons

varchar

2147483647

√

null

Patient Death Status

varchar

√

null

View Definition

CREATE VIEW [dbo].[BIvwAOMSCC]
		AS
	SELECT 
-- 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],

--formats and shows assessment date and time
CONVERT(varchar(10), [tblAO_MSCC].[AssessmentDate], 103) as [Assessment Date], CONVERT(varchar(5), [tblAO_MSCC].[AssessmentTime]) as [Assessment Time], 

--statement to select names of each CNS. achived by looking at the link table and selecting values where the id matches the one stored in the AO assessment table.
(SELECT ltblCNS_NAME.CNS_NAME + ', ' AS 'data()'
FROM lnkAO_ASSESSED_BY_CNS_MSCC
JOIN ltblCNS_NAME ON ltblCNS_NAME.ID = lnkAO_ASSESSED_BY_CNS_MSCC.CnsID
WHERE lnkAO_ASSESSED_BY_CNS_MSCC.ReferralID = tblAO_MSCC.ReferralID
ORDER BY CnsID ASC
FOR XML PATH('')) as [Assessed By CNS],

--statement to select names of each consultant CNS. achived by looking at the link table and selecting values where the id matches the one stored in the AO assessment table.
(SELECT ltblCONSULTANTS.CON_DESC + ', ' AS 'data()'
FROM lnkAO_ASSESSED_BY_CONSULTANT_MSCC
JOIN ltblCONSULTANTS ON ltblCONSULTANTS.CON_ID = lnkAO_ASSESSED_BY_CONSULTANT_MSCC.ConsultantID
WHERE lnkAO_ASSESSED_BY_CONSULTANT_MSCC.ReferralID = tblAO_MSCC.ReferralID
ORDER BY ConsultantID ASC
FOR XML PATH('')) as [Assessed By Consultant],

--Uses linked table to find Name of Oncologist/Haematologist
ltblCONSULTANTS.CON_DESC as [Name of Oncologist/Haematologist],

--shows contents of Record of Discussions with Treatment Team text box
tblAO_MSCC.TreatmentTeamsDiscussions as [Record of Discussions with Treatment Team(s)],

--shows if steroids have started, what date they were started on and their type.
CASE tblAO_MSCC.SteroidsStartedYN
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Steroids Started?],

tblAO_MSCC.SteroidsStartedDate as [Date Steroids Started], tblAO_MSCC.SteroidsType as [Type of Steroids Administered],

 -- 1yes 0no 2notknown
CASE tblAO_MSCC.NursedFlat
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	WHEN 3 THEN 'Not Known'
	END AS [Nursed Flat Prior to Referral],

--Shows checkboxes in readable form (1 + yes 0 + no)
CASE tblAO_MSCC.ImagingCT
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Imaging Requested:CT],
	
CASE tblAO_MSCC.ImagingMRI
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Imaging Requested:MRI],
	
CASE tblAO_MSCC.ImagingPET
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Imaging Requested:PET],
	
CASE tblAO_MSCC.ImagingOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_MSCC.ImagingOtherText, '')
	END AS [Imaging Requested:Other],

--formats and shows dates and times
CONVERT(varchar(10), CAST([tblAO_MSCC].[ImagingRequestedDate] as DATETIME), 103) as [Imaging Requested Date], CONVERT(varchar(5), CAST([tblAO_MSCC].[ImagingRequestedTime]As DATETIME)) as [Imaging Requested Time], 
CONVERT(varchar(10), [tblAO_MSCC].[ImagingPerformedDate], 103) as [Imaging Performed Date], CONVERT(varchar(5), [tblAO_MSCC].[ImagingPerformedTime]) as [Imaging Performed Time], 

DATEDIFF(DD,CAST(ImagingRequestedDate as DATETIME) + CAST(ImagingRequestedTime AS DATETIME) ,CAST(ImagingPerformedDate as DATETIME) + CAST(ImagingPerformedTime AS DATETIME) ) [Time Lapse (Requested to Performed)] ,

CONVERT(varchar(10), [tblAO_MSCC].[ImagingReportedDate], 103) as [Imaging Reported Date], CONVERT(varchar(5), [tblAO_MSCC].[ImagingReportedTime]) as [Imaging Reported Time], 

DATEDIFF(DD,CAST(ImagingPerformedDate as DATETIME) + CAST(ImagingPerformedTime AS DATETIME) ,CAST(ImagingReportedDate as DATETIME) + CAST(ImagingReportedTime AS DATETIME) ) [Time Lapse (Performed to Reported)] ,

--uses link table to find Reporting Organisation
ReportingOrganisation.[Description] as [Reporting Organisation],

CASE tblAO_MSCC.ImagingMSCCConfirmedYN
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + CONVERT(varchar(10), [tblAO_MSCC].[ImagingMSCCConfirmedDate], 103), '')
	END AS [MSCC Confirmed?],

--formats dates
CONVERT(varchar(10), [tblAO_MSCC].[ImagingSpinalSurgeonsInformedDate], 103) as [Date Spinal Surgeons Informed],

CONVERT(varchar(10), [tblAO_MSCC].[ImagingRadiotherapyInformedDate], 103) as [Date Radiotherapy Informed],

--shows contents of Diagnosis Delay Reasons
ImagingDiagnosisDelayReasons as [Diagnosis Delay Reasons],

CASE tblAO_MSCC.PTSurgery
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Primary Treatment Type:Surgery],
	
CASE tblAO_MSCC.PTRadiotherapy
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Primary Treatment Type:Radiotherapy],
	
CASE tblAO_MSCC.PTChemotherapy
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Primary Treatment Type:Chemotherapy],
	
CASE tblAO_MSCC.PTBestSupportiveCare
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END AS [Primary Treatment Type:Best Supportive Care],
	
CASE tblAO_MSCC.PTOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_MSCC.PTOtherPrimary, '')
	END AS [Primary Treatment Type:Other],

CONVERT(varchar(10), [tblAO_MSCC].[PTStartDate], 103) as [Treatment Start Date],

CONVERT(varchar(5), [tblAO_MSCC].[PTStartTime]) as [Treatment Start Time],

DATEDIFF(DD,CAST(ImagingRequestedDate as DATETIME) + CAST(ImagingRequestedTime AS DATETIME) ,CAST(PTStartDate as DATETIME) + CAST(PTStartTime AS DATETIME) ) [Time Lapse (Imaging Request To Treatment Start)] ,

DATEDIFF(DD,CAST(ImagingPerformedDate as DATETIME) + CAST(ImagingPerformedTime AS DATETIME) ,CAST(PTStartDate as DATETIME) + CAST(PTStartTime AS DATETIME) ) [Time Lapse (Imaging Performed To Treatment Start)] ,


PTOrganisation.[Description] as [Primary Treatment Organisation],

--Shows contents of Treatment Delay Reasons
PTDelayReasons,

--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_MSCC on tblAO_REFERRAL.ReferralID = tblAO_MSCC.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 consultants table
LEFT JOIN ltblCONSULTANTS on tblAO_MSCC.OncologistHaematologist = ltblCONSULTANTS.CON_ID

--joins org codes table to obtain org desc
LEFT JOIN OrganisationSites ReportingOrganisation ON tblAO_MSCC.ImagingReportingOrganisation = ReportingOrganisation.ID
LEFT JOIN OrganisationSites PTOrganisation ON tblAO_MSCC.PTOrganisation = PTOrganisation.ID

Columns

View Definition

Possibly Referenced Tables/Views