Red2Green Tracker and Dashboard

In order to track and monitor the areas and patients that are utilising the Red2Green methodology, we first needed to capture the data. A simple two-tab worksheet was designed for an analyst to populate with ward and patient information, and make available for staff on the included areas to update with a patients Red or Green status, along with a reason.

A blank template (Appendix A) was designed on the first tab, as below:

For clarity, the fields are:

  • NHS Number (to identify patients across platforms, if needed)
  • Patient Name(to identify patients on the ward)
  • Age at Admit(patients age at point of admission)
  • Bed Number(to differentiate between patients of the same name, also to sort by walking order)
  • Current Ward(to facilitate staff in quickly identifying their own patient list)
  • Current LoS(to monitor length of the patient’s whole spell to date in hospital)
  • Day Colour(all default to Red on report creation)
  • Delay Reason(a pre-determined list of ~35 items to cover all delay categories)
  • Gender(additional patient information)
  • Admit Date(to allow the calculation of current length of stay)
  • MFFD? Y/N(to demonstrate which patients are Medically Fit for Discharge each day)
  • MFFD Date(for those that are medically fit, what date were they declared MFFD, or “Not MFFD”)
  • Patient Area (CCG)(to identify patients from our local CCG against “Out of Area” patients)
  • Further Comments(to clarify if a delay reason is too broad in a specific situation)

An SQL script (Appendix B) extracts data for the above items (except for Delay Reason and Further Comments). The script is run each morning between 8am and 8:30am each morning, and the script queries the database as at midnight the previous night to see which patients were still admitted overnight. This patient list, and the accompanying data items listed above, form the basis of the Red2Green report for that day.

Towards the end of the day, the completed data is finalised to correct any data gaps or errors. A completed dataset (with patient identifiable data removed) looks like the below:

On the second tab, a dashboard has been developed (Appendix C). This dashboard is comprised of three indicators that can be filtered down to a ward level:

  • Pie Chart showing percentage split of Red, Green, and where data has been unavailable for inclusion, Black
  • Column Chart showing the total number of Red days per ward
  • Table and Column Chart showing the total number of each of the pre-determined Delay Reasons

The dashboards are a set of pivot tables and pivot charts based on the data collected earlier by the ward staff. Pivot tables and charts will not automatically update when new data is added, and people not familiar with pivoting data may not be comfortable with the process of updating tables manually, so a button was coded and added to the top of the data collection worksheet. Once the data is collected and inputted, a user only needs to click this button, and a coded macro module (Appendix D) will automatically run the process for updating all tables and charts.

Once a week, all of the daily sheets are combined into one master version, and from this, we produce a 14-Day Pathway ‘at a glance’ for all patients, highlighting their status each day (Appendix E). This is done by coding a reference key field against each record in the master sheet, and running a VLOOKUP formula to pull each patients Red, Green or Black status for each day. These daily status’ are colour coded for ease of viewing, and a status is also added against each patient to say whether they are currently still admitted, or have been discharged.

Please Note: This Pathway At A Glance is currently in the process of being automated, so that end users will be able to perform all the above actions for themselves. This development is expected to take approximately two weeks.

Appendix A

Appendix B

UseOasis_Import

Select DistinctPI.ID_Number as 'NHS Number',

PM.Pat_Name_1 + ' ' + PM.Pat_Name_Family as 'Patient Name',

DateDiff(YY, PM.Date_Of_Birth, PA.Admit_Date) as 'Age at Admit',

'Bed' + ' ' + BD.Bed_Location as 'Bed Number',

Case When WED.Description like '%A3%' Then 'A3'

When WED.Description like '%B6%' Then 'B6'

When WED.Description like '%C1%' Then 'C1'

When WED.Description like '%C3%' Then 'C3'

When WED.Description like '%C4%' Then 'C4'

When WED.Description like '%C5%' Then 'C5'

When WED.Description like '%C7%' Then 'C7'

When WED.Description like '%C8%' Then 'C8'

When WED.Description like '%B2%' Then 'B2'

When WED.Description like '%C6%' Then 'C6'

When WED.Description like '%B3%' Then 'B3'

When WED.Description like '%A2%' Then 'A2'

When WED.Description like '%P%CCU%' Then 'Post CCU' Else '' End as 'Current Ward',

DateDiff(DD, PA.Admit_Date, GetDate()) as 'Current LoS',

'Red' as 'Day Colour',

'' as 'Delay Reason',

Case When PM.Sex = 'M' Then 'Male' Else 'Female' End as 'Gender',

PA.Admit_Date as 'Admit Date',

Case When PA.Clinical_Discharge_Date Is Null Then 'No' Else 'Yes' End as 'MFFD? Y/N',

Case When PA.Clinical_Discharge_Date Is Null Then '' Else PA.Clinical_Discharge_Date End as 'MFFD Date',

Case When C.Description like '%Dudley%' Then 'Dudley'

When C.Description like '%Wolverhampton%' Then 'Wolves'

When C.Description like '%Walsall%' Then 'Walsall'

When C.Description like '%Sandwell%WestBirm%' Then 'Sandwell'

When C.Description like '%Cross%' Then 'Birm CC'

When C.Description like '%Bromsgrove%' Then 'Redd&Broms'

When C.Description like '%Wyre Forest%' Then 'Wyre Forest'

When C.Description like '%Seisdon%' Then 'Seisdon&Staffs'

When C.Description like '%Birmingham South%' Then 'Birm S&C'

When C.Description like '%Shropshire%' Then 'Shropshire' Else 'Other CCG' End as 'Patient Area (CCG)',

'' as 'Further Comments'

FromPatient_Ad PA

JoinPatient_IDs PI on PA.Patient_ID = PI.Patient_ID and PI.ID_Type_Code = 56970

JoinPatient_Master PM on PA.Patient_ID = PM.Patient_ID

JoinBed_Details BD on PA.Admission_No = BD.Admission_No and BD.Current_Record = 'Y'

JoinPatient_Care_Episodes PCE on BD.Care_Episode_ID = PCE.Care_Episode_ID

JoinWork_Entity_Data WED on BD.Work_Entity = WED.Work_Entity

JoinGP_Practice_Master GP on PM.Practice_ID = GP.Practice_ID

JoinCodes C on GP.PCG_Code = C.Code

WherePA.Physical_Discharge_Date Is Null

AndPA.Admit_Type = 'I'

And(WED.Description like '%A3%'

OrWED.Description like '%B6%'

OrWED.Description like '%C1%'

OrWED.Description like '%C3%'

OrWED.Description like '%C4%'

OrWED.Description like '%C5%'

OrWED.Description like '%C7%'

OrWED.Description like '%C8%'

OrWED.Description like '%B2%'

OrWED.Description like '%C6%'

OrWED.Description like '%B3%'

OrWED.Description like '%A2%'

OrWED.Description like '%P%CCU%')

Order ByCase When WED.Description like '%A3%' Then 'A3'

When WED.Description like '%B6%' Then 'B6'

When WED.Description like '%C1%' Then 'C1'

When WED.Description like '%C3%' Then 'C3'

When WED.Description like '%C4%' Then 'C4'

When WED.Description like '%C5%' Then 'C5'

When WED.Description like '%C7%' Then 'C7'

When WED.Description like '%C8%' Then 'C8'

When WED.Description like '%B2%' Then 'B2'

When WED.Description like '%C6%' Then 'C6'

When WED.Description like '%B3%' Then 'B3'

When WED.Description like '%A2%' Then 'A2'

When WED.Description like '%P%CCU%' Then 'Post CCU' Else '' End,

PM.Pat_Name_1 + ' ' + PM.Pat_Name_FamilyAsc

Appendix C

Appendix D

Appendix E