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.

We are currently prioritising COVID-19 related reporting work and therefore the production of some of our routine reports has been delayed. These will resume as soon as possible.


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:

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"));

Then run this query:

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);

Last edited: 25 November 2021 11:43 am