January 24, 2019 at 8:44 am
I could use some guidance on how to construct the following query. I need to derive a count of agents logged in for each hour interval of the day by Campaign. Note some agents might log in and out several times during the day.
If the agent is logged in for any part of the hour, I want to count them as logged in. I also may have cross day divide situations, where the agent starts shift late in evening and ends early next morning. I'm not looking for someone to do the work for me, I just need a little help figuring out what the best approach may be. Any suggestion is greatly appreciated.
I have Agent ID, Logged in DateTime, Logged Out DateTime, and Campaign.
Most greatful!!!
Cervello
January 24, 2019 at 10:08 am
There may be a better approach to this out there but this works and can at least give you a base to start with.
Create Table #test (agentid int , startdt datetime, enddt datetime)
Create Table #hours (hr int)
insert into #hours (hr) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14)
,(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)
insert into #test values (1, '2019-01-24 10:40:47.890','2019-01-24 11:40:47.890') -- 10 and 11
,(1, '2019-01-24 6:00:47.890','2019-01-24 8:40:47.890') --6,7,8
,(2, '2019-01-23 22:40:47.890','2019-01-24 7:40:47.890') --22,23,24,1,2,3,4,5,6,7
,(3, '2019-01-24 9:23:47.890','2019-01-24 9:24:47.890') --9
Select *
From (
Select Cast(StartDt as Date) Dts From #test
Union
Select Cast(EndDt as Date) Dts From #test
) d
Join #hours h on 1 = 1
Outer Apply (
Select Count(Distinct agentid) AgentsLoggedIn
From #test t
Where (h.hr between datepart(hour,startdt) and case when day(enddt) > day(startdt) then 24 else datepart(hour,enddt) end and d.Dts = Cast(startdt as date))
or
(h.hr between case when day(enddt) > day(startdt) then 0 else datepart(hour,startdt) end and datepart(hour,enddt) and d.Dts = Cast(enddt as date))
) q
January 24, 2019 at 11:28 am
Thank you for getting back to me so quickly. I believe that should work wonderfully!!! I've run the test and all looks good, just walking through the code now to get a better understanding of what it is doing.
Thank you again for all your help!!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply