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

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" 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: 12 August 2019 10:11 am