SMH TW and SCMAgent Usage
IF OBJECT_ID('tempdb..#agentreport') IS NOT NULL
DROP TABLE #ehragent
IF OBJECT_ID('tempdb..#ehragent') IS NOT NULL
DROP TABLE #agentreport
SELECT UserId,
CASE
WHEN WeekDayName = 'Mon' then '1-Monday'
WHEN WeekDayName = 'Tue' then '2-Tuesday'
WHEN WeekDayName = 'Wed' then '3-Wednesday'
WHEN WeekDayName = 'Thu' then '4-Thursday'
WHEN WeekDayName = 'Fri' then '5-Friday'
WHEN WeekDayName = 'Sat' then '6-Saturday'
WHEN WeekDayName = 'Sun' then '7-Sunday' else '' END as DoW,
CASE
WHEN upper(UserID) like '%SMH.COM' then 'SCM'
WHEN upper(UserID) like '%ALLSCRIPTS.COM' then 'TW' else '' END as UserType,
EventDefName
into #ehragent
FROM [dbmADRLV3].[dbo].[LV3_TrcAction]
where EventDefID in (62801,62802,62803,62809,62810,62816,62817)
/*
62801 - Published when user pressed the EHR Agent to expanded mode.Where he can view the patient data.
62802 - Published when user minimize theEHR Agent to Tray (either press the [X] or Menu?Close).
62803 - Published when user opened a clinical category within the EHR Agent.
62805 - Published when user pressed on “Send to my EHR” button.
62808 - Published when user sends feedback and clicks the Send button.
62809 - Occurs when the user opens Clinical Views window.
62810 - Occurs when the user switches to a new patient.
62811 - Occurs when an error in the EHR Agent occurs
62816 - Published when user context change occurs in EHR Agent.
62817- Published when the user closesthe EHR Agent.
*/
AND EventDate >= '2016-11-01 00:00:00' and EventDate <= '2016-12-13 23:59:59'
SELECT UserID, UserType, COUNT (UserID) as "ExpandedAgent" into #agentreport from #ehragent
where EventDefName = 'EHR Agent expanded mode' --clicked on agent
group by UserID, UserType
SELECT UserID, UserType, COUNT (UserID) as "PatientsViewed" into #agentreport2 from #ehragent
where EventDefName = 'Patient context change' --Patient Context
group by UserID, UserType
SELECT UserID, UserType, COUNT (UserID) as "LaunchesCV" into #agentreport3 from #ehragent
where EventDefName = 'User opens a CV page from EHR Agent' --opened CV
group by UserID, UserType
SELECT UserID, UserType, COUNT (UserID) as "SendToMyEHR" into #agentreport4 from #ehragent
where EventDefName = 'SendtoMyEHR' --SendtoMyEHR
group by UserID, UserType
SELECT A.UserID, A.UserType, A.ExpandedAgent, B.PatientsViewed, C.LaunchesCV, D.SendToMyEHR
from #agentreport A
left outer join #agentreport2 B
on (A.UserID = B.UserID)
left outer join #agentreport3 C
on (A.UserID = C.UserID)
left outer join #agentreport4 D
on (A.UserID = D.UserID)
order by 2 asc, 4 desc, 3 desc -- clicked the most
drop TABLE #ehragent
drop TABLE #agentreport
drop TABLE #agentreport2
drop TABLE #agentreport3
drop TABLE #agentreport4