SQL Server 2008 stored procedure - run once a day

Question

I am trying to run this query for every day over the past year. Is there a way to automate the process and get back a list of MaxAgentsLoggedIn for each day?

Thanks in advance

select top 1 
     dIntervalStart IntervalStart, count(cName) MaxAgentsLoggedIn
from IAgentQueueStats
where cReportGroup = '*'
   and cHKey3 = '*'
   and cHKey4 = '*'
   and tAgentLoggedIn > 0
   and dIntervalStart >= '2013-09-24 00:00:00'
   and dIntervalStart <= '2013-09-24 23:59:59'
group by 
   nDuration, dIntervalStart
order by 
   MaxAgentsLoggedIn desc;

Definition

      [cName] [nvarchar](50) NOT NULL,
   [cReportGroup] [nvarchar](50) NOT NULL,
[cHKey3] [nvarchar](50) NULL,
[cHKey4] [nvarchar](50) NULL,
[cType] [char](1) NOT NULL,
[dIntervalStart] [datetime] NOT NULL,
[nDuration] [int] NOT NULL,
[nEnteredAcd] [int] NOT NULL,
[nAbandonedAcd] [int] NOT NULL,
[nGrabbedAcd] [int] NOT NULL,
[nLocalDisconnectAcd] [int] NOT NULL,
[nAlertedAcd] [int] NOT NULL,
[nAnsweredAcd] [int] NOT NULL,
[nAnswered] [int] NOT NULL,
[nAcdSvcLvl] [int] NOT NULL,
[nAnsweredAcdSvcLvl1] [int] NOT NULL,
[nAnsweredAcdSvcLvl2] [int] NOT NULL,
[nAnsweredAcdSvcLvl3] [int] NOT NULL,
[nAnsweredAcdSvcLvl4] [int] NOT NULL,
[nAnsweredAcdSvcLvl5] [int] NOT NULL,
[nAnsweredAcdSvcLvl6] [int] NOT NULL,
[nAbandonAcdSvcLvl1] [int] NOT NULL,
[nAbandonAcdSvcLvl2] [int] NOT NULL,
[nAbandonAcdSvcLvl3] [int] NOT NULL,
[nAbandonAcdSvcLvl4] [int] NOT NULL,
[nAbandonAcdSvcLvl5] [int] NOT NULL,
[nAbandonAcdSvcLvl6] [int] NOT NULL,
[tGrabbedAcd] [int] NOT NULL,
[tAnsweredAcd] [int] NOT NULL,
[mtAnsweredAcd] [int] NOT NULL,
[tAbandonedAcd] [int] NOT NULL,
[tTalkAcd] [int] NOT NULL,
[tTalkCompleteAcd] [int] NOT NULL,
[nHoldAcd] [int] NOT NULL,
[tHoldAcd] [int] NOT NULL,
[nAcw] [int] NOT NULL,
[tAcw] [int] NOT NULL,
[tAcwComplete] [int] NOT NULL,
[nExternToInternCalls] [int] NOT NULL,
[nExternToInternAcdCalls] [int] NOT NULL,
[nInternToExternCalls] [int] NOT NULL,
[nInternToExternAcdCalls] [int] NOT NULL,
[nInternToInternCalls] [int] NOT NULL,
[nInternToInternAcdCalls] [int] NOT NULL,
[tExternToInternCalls] [int] NOT NULL,
[tExternToInternAcdCalls] [int] NOT NULL,
[tInternToExternCalls] [int] NOT NULL,
[tInternToExternAcdCalls] [int] NOT NULL,
[tInternToInternCalls] [int] NOT NULL,
[tInternToInternAcdCalls] [int] NOT NULL,
[nAcwCalls] [int] NOT NULL,
[tAcwCalls] [int] NOT NULL,
 [nTransferedAcd] [int] NOT NULL,
[nNotAnsweredAcd] [int] NOT NULL,
[tAlertedAcd] [int] NOT NULL,
[nFlowOutAcd] [int] NOT NULL,
[tFlowOutAcd] [int] NOT NULL,
[nStartWaitAlertAcdCalls] [int] NOT NULL,
[nStartActiveAcdCalls] [int] NOT NULL,
[nStartHeldAcdCalls] [int] NOT NULL,
[nEndWaitAlertAcdCalls] [int] NOT NULL,
[nEndActiveAcdCalls] [int] NOT NULL,
[nEndHeldAcdCalls] [int] NOT NULL,
[nTransferWithinAcdCalls] [int] NOT NULL,
[nTransferOutAcdCalls] [int] NOT NULL,
[nDisconnectAcd] [int] NOT NULL,
[tAgentLoggedIn] [int] NOT NULL,
[tAgentAvailable] [int] NOT NULL,
[tAgentTalk] [int] NOT NULL,
[tAgentOtherBusy] [int] NOT NULL,
[tAgentOnAcdCall] [int] NOT NULL,
[tAgentOnOtherAcdCall] [int] NOT NULL,
[tAgentInAcw] [int] NOT NULL,
[tAgentOnNonAcdCall] [int] NOT NULL,
[tAgentDnd] [int] NOT NULL,
[tAgentNotAvailable] [int] NOT NULL,
[tAgentAcdLoggedIn] [int] NOT NULL,
[tAgentStatusDnd] [int] NOT NULL,
[tAgentStatusAcw] [int] NOT NULL,
[tAgentLoggedInDiluted] [int] NOT NULL,
[tStatusGroupFollowup] [int] NOT NULL,
[tStatusGroupBreak] [int] NOT NULL,
[tStatusGroupTraining] [int] NOT NULL,
[CustomValue1] [int] NOT NULL,
[CustomValue2] [int] NOT NULL,
[CustomValue3] [int] NOT NULL,
[CustomValue4] [int] NOT NULL,
[CustomValue5] [int] NOT NULL,
[CustomValue6] [int] NOT NULL,
[I3TimeStampGMT] [datetime] NOT NULL,
[SiteId] [smallint] NOT NULL,
[SubSiteId] [smallint] NOT NULL,
[nLocalDisconnectAgentAlertAcd] [int] NULL,
[nLocalDisconnectAgentAcd] [int] NULL,
[tAgentAcdLoggedIn2] [int] NULL

This discussion shows how to schedule the stored procedure. http://stackoverflow.com/questions/287060/scheduled-run-of-stored-procedure-on-sql-server — user2601995, Sep 26 '13 at 02:33
What type of DB? In an Oracle DB, use a dbms_job tied to a Stored Procedure, in mySQL, look into this doc: http://dev.mysql.com/doc/refman/5.1/en/events-overview.html — Roberto Navarro, Sep 26 '13 at 02:35

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

Rather than iterating the query by day, can you include the day in the query and then group by the day?

Question 113045 has an answer that shows how to pull just the date from the date time using

convert(date, dIntervalStart)

If you add this date to your query, you can then query the max(AgentsLoggedIn) from the result and get a list by day.

select theDay, max(AgentsLoggedIn) MaxAgentsLoggedIn
from (
    select convert(date, dIntervalStart) as theDay
         dIntervalStart IntervalStart, count(cName) AgentsLoggedIn
    from IAgentQueueStats
    where cReportGroup = '*'
       and cHKey3 = '*'
       and cHKey4 = '*'
       and tAgentLoggedIn > 0
       and dIntervalStart >= '2013-09-24 00:00:00'
       and dIntervalStart <= '2013-09-24 23:59:59'
    group by 
       nDuration, dIntervalStart ) as q
group by theDay;

edited May 23 '17 at 12:28

Community

1
1

answered Sep 26 '13 at 02:57

GregA100k

1,385
1
11
16

Hi the tAgentloggedin is the amount of time the agent stayed logged in not the timestamp. The queury works correctly for one day. I am just trying to run for multiple days. – user2817598 Sep 26 '13 at 15:30
Can you edit the question to add the table definition - at least the definition of the relevant columns? That might make it easier to help. – GregA100k Sep 26 '13 at 15:52
Hi G_A. I have added the table definition – user2817598 Sep 26 '13 at 18:30

SQL Server 2008 stored procedure - run once a day

1 Answers1