-- This takes 1:14 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go /* ****************************************************************** Stored Procedure usp_DispositionSummaryByTeamAnalyst_sel ****************************************************************** Original Author: Keith Payne Creation Date: 05/09/2004 Work Order: 299057 ********************************************************************* Purpose: Select calls inbound and calls disposed from the Genesys system. Total the calls by Customer Service Center team. Subtotal by individual analyst Results are filtered by the call date. NOTE: A selection of Team or Analyst is required. The source data is not normalized and will yield incorrect aggregates if the report is run for all teams and analysts. Revision History: 05/18/2005 Removed the call date 09/28/2005 Rewrote to include all Analysts within the team. Default for @TeamID is 158 - All Agents ********************************************************************* How to Use: Login as dbo or equivalent in Query Analyzer. To select by team: EXEC usp_DispositionSummaryByTeamAnalyst_sel '2005-01-01', '2005-01-28', 216, NULL To select by analyst: EXEC usp_DispositionSummaryByTeamAnalyst_sel '20050901', '20050921', NULL, 409 */ --------------------------------------------------------------------------------------------- ALTER PROCEDURE [dbo].[usp_DispositionSummaryByTeamAnalyst_sel] ( @FromDate datetime, @ToDate datetime, @TeamID int = 158, @AnalystID int = NULL ) AS IF NOT (@FromDate IS NOT NULL AND @ToDate IS NOT NULL AND @ToDate >= @FromDate) BEGIN RAISERROR('Parameters @FromDate and @ToDate are required. @FromDate must be <= @ToDate;usp_DispositionSummaryByTeamAnalyst_sel',16,1) RETURN END IF @TeamID IS NULL set @TeamID = 158 declare @Days int set @Days = DATEDIFF(dd,@FromDate, @ToDate) declare @Sequence table ([id] int) insert into @Sequence select [id] from [BA_Shared].dbo.udf_SequenceTable(@Days) declare @Detail table ( [TeamID] [numeric](10, 0) NULL, [TeamName] [varchar](240) NULL, [AnalystID] [numeric](10, 0) NULL, [AnalystName] [varchar](240) NULL, [LoginCode] [int] NULL, [CallDate] [varchar](30) NULL, [CallTime] [varchar](30) NULL, [AccountNumber] [varchar](16) NULL, [AccountName] [varchar](50) NULL, [CallerName] [varchar](50) NULL, [CallReason] [varchar](50) NULL, [ReasonCode] [int] NULL, [CallDisposition] [char](50) NULL, [DispositionCode] [int] NULL, [IsCallResolved] [char](1) NOT NULL ) insert into @Detail select * from dbo.udf_DispositionCodeDetail (@FromDate, @ToDate, @TeamID, @AnalystID) select t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived, SUM(CASE d.IsCallResolved WHEN 'Y' THEN 1 ELSE 0 END) as CallsResolved from dbo.vw_Genesys_TeamAnalystLogin t full join @Sequence s left join @Detail d inner join [dbo].[vw_Genesys_AgentCallStats] c on c.AnalystID = d.AnalystID and c.TeamID = d.TeamID and c.CallDate = d.CallDate on d.CallDate = DATEADD(dd,s.[id],@FromDate) on t.TeamID = d.TeamID and t.AnalystID = d.AnalystID where COALESCE(t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived) IS NOT NULL AND t.TeamID = @TeamID AND ((d.AnalystID = @AnalystID) OR d.AnalystID IS NULL AND @AnalystID IS NULL) group by t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived order by t.AnalystName, d.CallDate
-- This takes :12 declare @FromDate datetime, @ToDate datetime, @TeamID int, @AnalystID int set @FromDate = '20050901' set @ToDate = '20050921' set @TeamID = NULL set @AnalystID = 409 IF NOT (@FromDate IS NOT NULL AND @ToDate IS NOT NULL AND @ToDate >= @FromDate) BEGIN RAISERROR('Parameters @FromDate and @ToDate are required. @FromDate must be <= @ToDate;usp_DispositionSummaryByTeamAnalyst_sel',16,1) RETURN END IF @TeamID IS NULL set @TeamID = 158 declare @Days int set @Days = DATEDIFF(dd,@FromDate, @ToDate) declare @Sequence table ([id] int) insert into @Sequence select [id] from [BA_Shared].dbo.udf_SequenceTable(@Days) declare @Detail table ( [TeamID] [numeric](10, 0) NULL, [TeamName] [varchar](240) NULL, [AnalystID] [numeric](10, 0) NULL, [AnalystName] [varchar](240) NULL, [LoginCode] [int] NULL, [CallDate] [varchar](30) NULL, [CallTime] [varchar](30) NULL, [AccountNumber] [varchar](16) NULL, [AccountName] [varchar](50) NULL, [CallerName] [varchar](50) NULL, [CallReason] [varchar](50) NULL, [ReasonCode] [int] NULL, [CallDisposition] [char](50) NULL, [DispositionCode] [int] NULL, [IsCallResolved] [char](1) NOT NULL ) insert into @Detail select * from dbo.udf_DispositionCodeDetail (@FromDate, @ToDate, @TeamID, @AnalystID) select t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived, SUM(CASE d.IsCallResolved WHEN 'Y' THEN 1 ELSE 0 END) as CallsResolved from dbo.vw_Genesys_TeamAnalystLogin t full join @Sequence s left join @Detail d inner join [dbo].[vw_Genesys_AgentCallStats] c on c.AnalystID = d.AnalystID and c.TeamID = d.TeamID and c.CallDate = d.CallDate on d.CallDate = DATEADD(dd,s.[id],@FromDate) on t.TeamID = d.TeamID and t.AnalystID = d.AnalystID where COALESCE(t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived) IS NOT NULL AND t.TeamID = @TeamID AND ((d.AnalystID = @AnalystID) OR d.AnalystID IS NULL AND @AnalystID IS NULL) group by t.TeamID, t.TeamName, t.AnalystID, t.AnalystName, d.CallDate, c.CallsReceived order by t.AnalystName, d.CallDate