BIvwAOAssessment

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 Date

varchar

√

null

Referral Time

varchar

√

null

Assessment Date

varchar

√

null

Assessment Time

varchar

√

null

Time Lapse (Total days)

int

√

null

Time Lapse (Total hours)

int

√

null

Assessment Type

varchar

257

√

null

Assessment Location

varchar

√

null

Assessed By - CNS

nvarchar

1073741823

√

null

Assessed By - Consultant

nvarchar

1073741823

√

null

Contact With Patient

varchar

√

null

Contact With Carer

varchar

√

null

Contact With HCP

varchar

√

null

Contact With GP

varchar

√

null

Is Frailty Suspected?

varchar

√

null

Is a CGA required?

varchar

√

null

Performance Status (WHO)

int

√

null

Record Of Assessment

varchar

2147483647

√

null

Discussed With

varchar

2147483647

√

null

Significant Medical History

varchar

2147483647

√

null

Additional Notes

varchar

2147483647

√

null

Advice Given:Patient to attend A&E

varchar

√

null

Advice Given:Patient to attend an assessment unit

varchar

√

null

Advice Given:Patient to contact GP

varchar

√

null

Advice Given:Patient to receive a follow up call

varchar

√

null

Advice Given:Patient to attend pre-planned OPA

varchar

√

null

Advice Given:Patient to attend newly arranged OPA

varchar

√

null

Advice Given:Advised referral to another Team

varchar

√

null

Advice Given:Advised referral to another Hospital

varchar

√

null

Advice Given:Recommended diagnostic tests

varchar

√

null

Advice Given:Information given (HCP/GP)

varchar

√

null

Advice Given:Information given (Patient/Carer)

varchar

√

null

Advice Given:Psychological support (Patient/Carer)

varchar

√

null

Name of HCP Advice Was Given To

varchar

250

√

null

Additional Advice Given

varchar

2147483647

√

null

Actions:Symptom management

varchar

√

null

Actions:Arrange Early/Fast Track Clinic appointment

varchar

√

null

Actions:Arrange Routine Site Specific F/U

varchar

√

null

Actions:Arrange Urgent Site Specific F/U

varchar

√

null

Actions:Patient admitted to Hospital

varchar

√

null

Actions:Taken over by AOS or Oncology

varchar

√

null

Actions:Further AO review as Inpatient

varchar

√

null

Actions:Follow up call to be made within 24 hours

varchar

√

null

Actions:No further AO F/U planned

varchar

√

null

Actions:Referred to Hospital Palliative Care Team

varchar

√

null

Actions:Referred to Community Palliative Care Team

varchar

√

null

Actions:Referred to GP

varchar

√

null

Actions:Referred to other service

varchar

255

√

null

Tests Cancelled?

varchar

√

null

Tests Cancelled:Biopsy

varchar

√

null

Tests Cancelled:Bloods

varchar

√

null

Tests Cancelled:CT

varchar

√

null

Tests Cancelled:MRI

varchar

√

null

Tests Cancelled:PET

varchar

√

null

Tests Cancelled:Bone Scan

varchar

√

null

Tests Cancelled:Other

varchar

255

√

null

Additional Action Taken

varchar

2147483647

√

null

Patient Death Status

varchar

√

null

UKONS Score V2-0

varchar

√

null

View Definition

CREATE VIEW [dbo].[BIvwAOAssessment]
		AS
WITH RequiredData AS (
       SELECT
              ao.AssessmentID,
              ao.UkonsScore,
              AmberLow,
              RedLow
       FROM
              lnkAO_ASSESSMENT_RESULT ao
       JOIN ltblAO_UKONS_SCORE ukons ON
              ukons.UkonsID = ao.UkonsID
), GroupedData AS (
       SELECT
              AssessmentID,
              --SUM(CASE WHEN UkonsScore = 0 THEN 1 ELSE 0 END) AS IsEmpty,
              CASE WHEN Count(*) < 23 THEN 1 ELSE 0 END AS IsEmpty,
              SUM(CASE WHEN UkonsScore >= AmberLow THEN 1 ELSE 0 END) AS IsAmber,
              SUM(CASE WHEN UkonsScore >= RedLow THEN 1 ELSE 0 END) AS IsRed
       FROM
              RequiredData
       GROUP BY
              AssessmentID
), Results AS (
SELECT
       AssessmentID,
       CASE
              WHEN IsEmpty = 1 THEN 'Incomplete'
              WHEN IsRed >= 1 THEN 'Red'
              WHEN IsAmber >= 2 THEN 'Red'
              WHEN IsAmber = 1 THEN 'Amber'
              ELSE 'Green'
       END AS Score
FROM
       GroupedData)

		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],

-- Shows REFERRAL Date And Time
CONVERT(varchar(10), [tblAO_REFERRAL].[ReferredDate], 103) as [Referral Date], CONVERT(varchar(5), [tblAO_REFERRAL].[ReferredTime]) as [Referral Time], 

--Displays assessment date and time
CONVERT(varchar(10), [tblAO_ASSESSMENT].[AssessmentDate], 103) as [Assessment Date], CONVERT(varchar(5), [tblAO_ASSESSMENT].[AssessmentTime]) as [Assessment Time], 

--calculates the diffrence between the refered time and the assessment time and displays it to the user.
DATEDIFF (DD, ReferredDate, AssessmentDate) as [Time Lapse (Total days)],

--calculates the diffrence between the refered time and the assessment time in hours and displays it to the user. commented out as hours are not allways provided making days more viable.
DATEDIFF(MINUTE,CAST(ReferredDate as DATETIME) + CAST(ReferredTime as DATETIME) ,CAST(AssessmentDate as DATETIME) + CAST(AssessmentTime AS DATETIME) ) / 60 [Time Lapse (Total hours)] ,
--shows assessment type
CASE ltblAO_ASSESSMENT_TYPE.AssessmentDesc
	WHEN 'Other' THEN 'Other' + isnull(', ' + AssessmentTypeOtherDesc, '')
	ELSE ltblAO_ASSESSMENT_TYPE.AssessmentDesc
	END as [Assessment Type],

--shows Assessment Location using lookup table
ltblPAL_WARDS.WARD as [Assessment Location],

--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
JOIN ltblCNS_NAME ON ltblCNS_NAME.ID = lnkAO_ASSESSED_BY_CNS.CnsID
WHERE lnkAO_ASSESSED_BY_CNS.AssessmentID = tblAO_ASSESSMENT.AssessmentID
ORDER BY CnsID ASC
FOR XML PATH('')) as [Assessed By - CNS],

--statement to select names of each consultant. 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
JOIN ltblCONSULTANTS ON ltblCONSULTANTS.CON_ID = lnkAO_ASSESSED_BY_CONSULTANT.ConsultantID
WHERE lnkAO_ASSESSED_BY_CONSULTANT.AssessmentID = tblAO_ASSESSMENT.AssessmentID
ORDER BY lnkAO_ASSESSED_BY_CONSULTANT.ConsultantID ASC
FOR XML PATH('')) as [Assessed By - Consultant],

--Uses case statement to make check box selection readable (1=yes 0=no)
CASE tblAO_ASSESSMENT.ContactWithPatient
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Contact With Patient],

CASE tblAO_ASSESSMENT.ContactWithCarer
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Contact With Carer],
	
CASE tblAO_ASSESSMENT.ContactWithHcp
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Contact With HCP],
	
CASE tblAO_ASSESSMENT.ContactWithGp
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Contact With GP],

-- Uses case statement to show if frailty is suspected
CASE tblAO_ASSESSMENT.IsFrailtySuspected
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Is Frailty Suspected?],

-- Uses case statement to show if a Comprehensive Geriatric Assessment (CGA) is required. note:long filed now shorten to "Is a CGA required?"?
CASE tblAO_ASSESSMENT.IsCgaRequired
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	WHEN 2 THEN 'Not Known'
	END as [Is a CGA required?],

--Guide to long to include
tblAO_ASSESSMENT.PerformanceStatus AS [Performance Status (WHO)], 

--displays the values of all the free text box inputs on the AO Assessment tab
tblAO_ASSESSMENT.RecordOfAssessment as [Record Of Assessment],
tblAO_ASSESSMENT.DiscussedWith as [Discussed With],
tblAO_ASSESSMENT.SignificantMedicalHistory as [Significant Medical History], 
tblAO_ASSESSMENT.AdditionalNotes as [Additional Notes], 

--shows what Advice Given check boxes are checked and shows them in english (ticked is yes and not ticked is no)
CASE tblAO_ASSESSMENT.AGAttendAE
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Patient to attend A&E],

CASE tblAO_ASSESSMENT.AGAttendAssessmentUnit
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Patient to attend an assessment unit],

CASE tblAO_ASSESSMENT.AGContactGP
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Patient to contact GP],

CASE tblAO_ASSESSMENT.AGContactedFollowUpCall
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Patient to receive a follow up call],

CASE tblAO_ASSESSMENT.AGAttendPrePlannedOPA
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Patient to attend pre-planned OPA],

CASE tblAO_ASSESSMENT.AGAttendNewlyArrangeOPA
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Patient to attend newly arranged OPA],

CASE tblAO_ASSESSMENT.AGAdvisedReferralToAnotherTeam
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Advised referral to another Team],

CASE tblAO_ASSESSMENT.AGAdvisedReferralToAnotherHospital
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Advised referral to another Hospital],

CASE tblAO_ASSESSMENT.AGRecommendedDiagnosticTests
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Recommended diagnostic tests],

CASE tblAO_ASSESSMENT.AGInfoGivenHcpGp
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Information given (HCP/GP)],

CASE tblAO_ASSESSMENT.AGInfoGivenPatientCarer
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Information given (Patient/Carer)],

CASE tblAO_ASSESSMENT.AGPsychologicalSupport
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Advice Given:Psychological support (Patient/Carer)],

-- shows the contents of the Name of HCP Advice Was Given To and Additional Advice Given text boxes
tblAO_ASSESSMENT.AGHcpName as [Name of HCP Advice Was Given To],

tblAO_ASSESSMENT.AGAdditional as [Additional Advice Given],

--shows what Advice Given check boxes are checked and shows them in english (ticked is yes and not ticked is no)
CASE tblAO_ASSESSMENT.ActionSymptomManagement
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Symptom management],

CASE tblAO_ASSESSMENT.ActionArrangeEarlyClinicAppointment
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Arrange Early/Fast Track Clinic appointment],
	
CASE tblAO_ASSESSMENT.ActionArrangeRoutineSiteSpecificFU
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Arrange Routine Site Specific F/U],

CASE tblAO_ASSESSMENT.ActionArrangeUrgentSiteSpecificFU
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Arrange Urgent Site Specific F/U],

CASE tblAO_ASSESSMENT.ActionPatientAdmittedToHospital
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Patient admitted to Hospital],

CASE tblAO_ASSESSMENT.ActionFurtherAOReviewAsInpatient
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Taken over by AOS or Oncology],

CASE tblAO_ASSESSMENT.ActionFurtherAOReviewAsInpatient
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Further AO review as Inpatient],

CASE tblAO_ASSESSMENT.ActionFollowUpCallToBeMadeWithin24Hours
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Follow up call to be made within 24 hours],

CASE tblAO_ASSESSMENT.ActionNoFurtherAOFUPlanned
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:No further AO F/U planned],

CASE tblAO_ASSESSMENT.ActionReferredToHospitalPalliativeCareTeam
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Referred to Hospital Palliative Care Team],

CASE tblAO_ASSESSMENT.ActionReferredToCommunityPalliativeCareTeam
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Referred to Community Palliative Care Team],

CASE tblAO_ASSESSMENT.ActionReferredToGP
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Actions:Referred to GP],

CASE tblAO_ASSESSMENT.ActionReferredToAnotherService
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_ASSESSMENT.ActionOtherService, '')
	END as [Actions:Referred to other service],

CASE tblAO_ASSESSMENT.TestsCancelled
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled?],

CASE tblAO_ASSESSMENT.TestsCancelledBiopsy
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled:Biopsy],

CASE tblAO_ASSESSMENT.TestsCancelledBloods
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled:Bloods],

CASE tblAO_ASSESSMENT.TestsCancelledCT
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled:CT],

CASE tblAO_ASSESSMENT.TestsCancelledMRI
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled:MRI],

CASE tblAO_ASSESSMENT.TestsCancelledPET
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled:PET],

CASE tblAO_ASSESSMENT.TestsCancelledBoneScan
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Tests Cancelled:Bone Scan],

CASE tblAO_ASSESSMENT.TestsCancelledOther
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes' + ISNULL(', ' + tblAO_ASSESSMENT.TestsCancelledOtherDesc, '')
	END as [Tests Cancelled:Other],

--shows the contents of the additional action taken text box
tblAO_ASSESSMENT.ActionAdditional as [Additional Action Taken], 

--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],
	
	Results.Score AS [UKONS Score V2-0] 

FROM tblAO_REFERRAL

-- MAIN TABLE JOINS
LEFT JOIN tblDEMOGRAPHICS on tblAO_REFERRAL.PatientID = tblDEMOGRAPHICS.PATIENT_ID
LEFT JOIN tblAO_ASSESSMENT on tblAO_REFERRAL.ReferralID = tblAO_ASSESSMENT.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 Wards lookup table
LEFT JOIN ltblPAL_WARDS ON tblAO_ASSESSMENT.AssessmentLocation = ltblPAL_WARDS.WARD_ID

--JOINS ASSESSMENT TYPES lookup TABLE
LEFT JOIN ltblAO_ASSESSMENT_TYPE ON tblAO_ASSESSMENT.AssessmentTypeID = ltblAO_ASSESSMENT_TYPE.AssessmentTypeID 

LEFT JOIN Results ON Results.AssessmentID = tblAO_ASSESSMENT.AssessmentID

Columns

View Definition

Possibly Referenced Tables/Views