Skip to main content
Creating a new NHS England: Health Education England, NHS Digital and NHS England have merged. More about the merger.

Appointment Slot Issue reports

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

To improve report production efficiency, data completeness and consistency, the ASI reports have been updated. As such, you will notice changes to several column names and an increase in data completeness throughout the report.

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:

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: 20 September 2023 2:11 pm