Skip to main content

Appointment Slot Issue reports

These reports provide detailed information on all Appointment Slot Issues (ASIs) generated from within the NHS e-Referral Service (eRS).

During the appointment booking process the NHS e-Referral Service will allow the referral to enter the ASI process if there are no slots available for booking at the time of the appointment search. This action is called 'Defer to Provider'. Each time the referral is deferred the referral will appear on the service providers 'Appointment Slot Issues' worklist. It is this action we are reporting.

Download the ASI reports

Archived reports

Generating the data

Find out how to generate the raw data of the ASI reports using your organisations latest extracts.

Extracts needed:

  • EBSX02
  • EBSX03
  • EBSX04
  • EBSX05

From EBSX03, create the following tables:

  • Specialties - usage filter = "SPECIALTY"
  • Priority - usage filter = "PRIORITY"
  • DeferReasons - usage filter =  "DEFERTOPROVIDERREASON"

From EBSX02, create a table called "ASI_Action_Codes" with this SQL:

COPY
SELECT UBRN_ID, SPECIALTY_CD, PRIORITY_CD, REFERRING_ORG_ID, ACTION_DT_TM, ACTION_CD, BUS_FUNCTION_CD, ACTION_REASON_CD, SERVICE_ID, NAMED_CLINICIAN_ID, UBRN

FROM EBSX02extractnamehere

WHERE (((ACTION_CD)="1430"));

SQL

Then run this query:

COPY
SELECT Mid([ASI_Action_Codes]![action_dt_tm],7,2) & "/" & Mid([ASI_Action_Codes]![action_dt_tm],5,2) & "/" & Left([ASI_Action_Codes]![action_dt_tm],4) AS [Date], Mid([ASI_Action_Codes]![action_dt_tm],9,2) & ":" & Mid([ASI_Action_Codes]![action_dt_tm],11,2) & ":" & Right([ASI_Action_Codes]![action_dt_tm],2) AS [Time], Priority.Display AS PRIORITY, ASI_Action_Codes.referring_org_id AS REFERRING_ORG_ID, ASI_Action_Codes.named_clinician_id AS NAMED_CLINICIAN_ID, DeferReasons.Display AS SLOT_PROBLEM_REASON, ASI_Action_Codes.service_id AS SLOT_PROBLEM_SERVICE_ID, Ebsx05.SERVICE_NAME AS SLOT_PROBLEM_SERVICE_NAME, Ebsx05.PROVIDER_ORG_ID AS SLOT_PROBLEM_SP_ORG_ID, Ebsx05.LOCATION_ORG_ID AS SLOT_PROBLEM_SP_SITE_ID, Ebsx04.ORG_NAME AS site_name, Specialties.DISPLAY AS SLOT_PROBLEM_SPECIALTY INTO ASI_DataSheet

FROM ((((ASI_Action_Codes INNER JOIN DeferReasons ON ASI_Action_Codes.action_reason_cd = DeferReasons.CODE) INNER JOIN Priority ON ASI_Action_Codes.priority_cd = Priority.CODE) INNER JOIN Ebsx05 ON ASI_Action_Codes.service_id = Ebsx05.SERVICE_ID) INNER JOIN Specialties ON Ebsx05.SPECIALTY_CD = Specialties.CODE) INNER JOIN Ebsx04 ON Ebsx05.LOCATION_ORG_ID = Ebsx04.ORG_ID

ORDER BY Mid([ASI_Action_Codes]![action_dt_tm],7,2) & "/" & Mid([ASI_Action_Codes]![action_dt_tm],5,2) & "/" & Left([ASI_Action_Codes]![action_dt_tm],4), Mid([ASI_Action_Codes]![action_dt_tm],9,2) & ":" & Mid([ASI_Action_Codes]![action_dt_tm],11,2) & ":" & Right([ASI_Action_Codes]![action_dt_tm],2);

SQL

Last edited: 24 March 2021 11:19 am