I cut some code out of a report I created for cycle time reporting. Since we use multiple appointments statuses that denote the same type of event, the first part of the script creates a temporary table that lists our statuses and the stage that they denote. The "WITH StatusChange" clause uses the ActivityLog table to aggregate the event times for each appointment, and the final SELECT statement creates an average for each day. I hope it's straightforward enough to modify for your own purposes.
DECLARE @StartDate DATETIME, @EndDate DATETIME, @ProviderId INT;
SELECT @StartDate = '2018-04-01';
SELECT @EndDate = '2018-04-30';
SELECT @ProviderId = 42;
DECLARE @ApptStatusStage TABLE (
Status VARCHAR(200)
, Stage VARCHAR(20)
);
INSERT INTO @ApptStatusStage VALUES ('Arrived', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Completed', 'Completed');
INSERT INTO @ApptStatusStage VALUES ('Consult Room', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Delay Insurance Problem', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Delay with Assister', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Delay with PAL or SS', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Delay with Receptionist', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Dental In Treatment', 'Roomed');
INSERT INTO @ApptStatusStage VALUES ('Done-Need to Retrieve Charges', 'Completed');
INSERT INTO @ApptStatusStage VALUES ('Exam Room 1', 'Roomed');
INSERT INTO @ApptStatusStage VALUES ('Exam Room 2', 'Roomed');
INSERT INTO @ApptStatusStage VALUES ('Exam Room 3', 'Roomed');
INSERT INTO @ApptStatusStage VALUES ('In Registration', 'Checked In');
INSERT INTO @ApptStatusStage VALUES ('Late Arrival Ready', 'Ready');
INSERT INTO @ApptStatusStage VALUES ('Ready', 'Ready');
WITH StatusChange AS (
SELECT AL.RecordId
, CheckedIn = MIN(CASE WHEN AST.Stage = 'Checked In' THEN AL.Created END)
, Ready = MIN(CASE WHEN AST.Stage = 'Ready' THEN AL.Created END)
, Roomed = MIN(CASE WHEN AST.Stage = 'Roomed' THEN AL.Created END)
, Completed = MIN(CASE WHEN AST.Stage = 'Completed' THEN AL.Created END)
, InsProblem = MAX(CASE WHEN AST.Status = 'Delay Insurance Problem' THEN 1 ELSE 0 END)
, PAL_SS = MAX(CASE WHEN AST.Status = 'Delay with PAL or SS' THEN 1 ELSE 0 END)
, ReceptDelay = MAX(CASE WHEN AST.Status = 'Delay with Receptionist' THEN 1 ELSE 0 END)
, AssisterDelay = MAX(CASE WHEN AST.Status = 'Delay with Assister' THEN 1 ELSE 0 END)
FROM
ActivityLog AS AL
INNER JOIN @ApptStatusStage AS AST ON AL.Value2 = AST.Status
WHERE AL.FunctionName = 'Change Appointment Status'
GROUP BY AL.RecordId
)
SELECT A.EmrApptStart
, AvgCycleTime = AVG(CAST(DATEDIFF(MI, SC.CheckedIn, SC.Completed) AS FLOAT))
FROM Appointments AS A
INNER JOIN StatusChange AS SC ON SC.RecordId = A.AppointmentsId
WHERE A.ApptKind = 1
AND ISNULL(A.ApptTypeId,0) NOT IN (37,87,28,26,91,27) -- VCC, Group visits
AND DATEDIFF(MI, SC.CheckedIn, SC.Completed) BETWEEN 1 AND 600
AND A.ResourceId = @ProviderId
AND A.EmrApptStart BETWEEN @StartDate AND @EndDate
GROUP BY A.EmrApptStart
Posted : May 8, 2018 2:47 am
