BIvwAONeutropenicSepsis

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

Suspected Date

varchar

√

null

Suspected Time

varchar

√

null

Arrival Date

varchar

√

null

Arrival Time

varchar

√

null

Assessment Date

varchar

√

null

Assessment Time

varchar

√

null

Date Antibiotics Prescribed

varchar

√

null

Time Antibiotics Prescribed

varchar

√

null

Date Antibiotics Administered

varchar

√

null

Time Antibiotics Administered

varchar

√

null

Door to Needle Time (minutes)

int

√

null

Antibiotics given within 60 minutes

varchar

√

null

Neutropenic Sepsis Confirmed?

varchar

√

null

Date Confirmed

varchar

√

null

Administration Service

varchar

257

√

null

Correct Medication Given?

varchar

√

null

Medication Given Comments

varchar

250

√

null

AntibioticGivenName

varchar

250

√

null

AntibioticGivenDose

varchar

250

√

null

ContributingFactors

varchar

2147483647

√

null

Patient Death Status

varchar

√

null

View Definition

CREATE VIEW [dbo].[BIvwAONeutropenicSepsis]
		AS

WITH NeutropenicSepsis AS (
	SELECT
		ReferralID,
		CONVERT(varchar(10), aons.[DateSuspected], 103) as SuspectedDate,
		CONVERT(varchar(5), aons.[TimeSuspected]) as SuspectedTime,
		CONVERT(varchar(10), aons.[DateArrival], 103) as ArrivalDate,
		CONVERT(varchar(5), aons.[TimeArrival]) as ArrivalTime,
		CONVERT(varchar(10), aons.[DateAssessment], 103) as AssessmentDate,
		CONVERT(varchar(5), aons.[TimeAssessment]) as AssessmentTime,
		CONVERT(varchar(10), aons.[DateAntibioticsPrescribed], 103) as DateAntibioticsPrescribed,
		CONVERT(varchar(5), aons.[TimeAntibioticsPrescribed]) as TimeAntibioticsPrescribed,
		CONVERT(varchar(10), aons.[DateAntibioticsAdministered], 103) as DateAntibioticsAdministered,
		CONVERT(varchar(5), aons.[TimeAntibioticsAdministered]) as TimeAntibioticsAdministered,
		CASE
			WHEN aons.DateArrival IS NOT NULL AND 
				aons.TimeArrival IS NOT NULL AND
				aons.DateAntibioticsAdministered IS NOT NULL AND
				aons.TimeAntibioticsAdministered IS NOT NULL
			THEN (DATEDIFF(MINUTE, aons.DateArrival, aons.DateAntibioticsAdministered) + 
				DATEDIFF(MINUTE, aons.TimeArrival, aons.TimeAntibioticsAdministered))
			ELSE NULL
		END DoorToNeedleTime,
		aons.ConfirmedYN AS ConfirmedYN,
		aons.DateConfirmed,
		aons.OtherService,
		aons.CorrectMedicationGivenYN,
		aons.CorrectMedicationGivenComment,
		aons.AntibioticGivenName,
		aons.AntibioticGivenDose,
		aons.ContributingFactors,
		aons.AdministrationServiceID
	FROM
		tblAO_NEUTROPENIC_SEPSIS aons
)
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],

ns.SuspectedDate as [Suspected Date],
ns.SuspectedTime as [Suspected Time], 
ns.ArrivalDate as [Arrival Date],
ns.ArrivalTime as [Arrival Time], 
ns.AssessmentDate as [Assessment Date],
ns.AssessmentDate as [Assessment Time], 
ns.DateAntibioticsPrescribed as [Date Antibiotics Prescribed],
ns.TimeAntibioticsPrescribed as [Time Antibiotics Prescribed], 
ns.DateAntibioticsAdministered as [Date Antibiotics Administered],
ns.TimeAntibioticsAdministered as [Time Antibiotics Administered], 

--calculates the diffrence between the presecribed date/time and the adminsitration date/time in mins NOTE:if the user puts any date before 1/1/1900 the dataview will not work
ns.DoorToNeedleTime [Door to Needle Time (minutes)] ,

CASE
	WHEN ns.DoorToNeedleTime <= 60 THEN 'Yes'
	WHEN ns.DoorToNeedleTime > 60 THEN 'No'
END AS [Antibiotics given within 60 minutes],

--shows if Neutropenic Sepsis is Confirmed
CASE ns.ConfirmedYN 
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Neutropenic Sepsis Confirmed?], 
	
CONVERT(varchar(10), ns.DateConfirmed, 103) as [Date Confirmed],

--shows Administration Service useing lookuptable
CASE ltblAO_ADMINISTRATION_SERVICE.AdministrationServiceDesc
	WHEN 'Other' THEN 'Other' + ISNULL(', ' + ns.OtherService, '')
	ELSE ltblAO_ADMINISTRATION_SERVICE.AdministrationServiceDesc
	END as [Administration Service],
	
--shows if the correct medication was given
CASE ns.CorrectMedicationGivenYN
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END as [Correct Medication Given?],

--shows comments made on medication given
ns.CorrectMedicationGivenComment as [Medication Given Comments],

--shows name and dose of antibiotic given
ns.AntibioticGivenName,
ns.AntibioticGivenDose,

--shows value of Contributing factors if antibiotic not administered in first hour text box
ns.ContributingFactors,

--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_NEUTROPENIC_SEPSIS ON tblAO_NEUTROPENIC_SEPSIS.ReferralID = tblAO_REFERRAL.ReferralID
LEFT JOIN NeutropenicSepsis ns ON
	ns.ReferralID = tblAO_REFERRAL.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 ADMINISTRATION_SERVICE table
LEFT JOIN ltblAO_ADMINISTRATION_SERVICE ON ltblAO_ADMINISTRATION_SERVICE.AdministrationServiceID = ns.AdministrationServiceID

Columns

View Definition

Possibly Referenced Tables/Views