DECLARE @SA BIGINT, @EA BIGINT, @SB BIGINT, @EB BIGINT SET @SA = {?StartAccount} SET @EA = {?EndAccount} SET @SB = {?StartBilling} SET @EB = {?EndBilling} -- test values --SET @SA = '1' --SET @EA = '9999999999' --SET @SB = '0' --SET @EB = '9999999999' declare @StartInfTime int, @EndInfTime int SET @StartInfTime = DATEDIFF(n, '12/31/1899', {?StartDate}) SET @EndInfTime = DATEDIFF(n, '12/31/1899', {?EndDate}) --test values --SET @StartInfTime = DATEDIFF(n, '12/31/1899', '2011-11-29 00:00:00') --SET @EndInfTime = DATEDIFF(n, '12/31/1899', '2011-11-30 00:00:00') DECLARE @ClientMaintChange int, @NewPatch int, @ParadoxTalk int SET @ClientMaintChange = {?ClientMaintChange} SET @NewPatch = {?NewPatch} SET @ParadoxTalk = {?ParadoxTalk} --Test data --SET @ClientMaintChange = 1 --SET @NewPatch = 0 --SET @ParadoxTalk = 1 DECLARE @NoOpAhold int, @NoOpDisc int, @NoOpGone int, @NoOpHold int, @NoOpPatch int, @NoOpRing int, @NoOpWait int, @NoOpZero int SET @NoOpAhold = {?NoOpAhold} SET @NoOpDisc = {?NoOpDisc} SET @NoOpGone = {?NoOpGone} SET @NoOpHold = {?NoOpHold} SET @NoOpPatch = {?NoOpPatch} SET @NoOpRing = {?NoOpRing} SET @NoOpWait = {?NoOpWait} SET @NoOpZero = {?NoOpZero} DECLARE @AnswerRing int, @AnswerAhold int, @AnswerZero int SET @AnswerRing = {?AnswerRing} SET @AnswerAhold = {?AnswerAhold} SET @AnswerZero = {?AnswerZero} --test values --SET @NoOpAhold = 0 --SET @NoOpDisc = 0 --SET @NoOpGone = 0 --SET @NoOpHold = 0 --SET @NoOpPatch = 0 --SET @NoOpRing = 0 --SET @NoOpWait = 1 --SET @NoOpZero = 0 --Get All of the Accounts declare @Accounts table (Account bigint) INSERT INTO @Accounts (Account) select DISTINCT Account FROM mCallBusied WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mCallEnd WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mClientMaint WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mConfBridge WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mDial WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mDialService WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mMessageTaken WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mMessageDelivered WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mModemOut WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mVoiceErased WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB UNION select DISTINCT Account FROM mWebCall WHERE [Timestamp] BETWEEN @StartInfTime AND @EndInfTime AND Account BETWEEN @SA AND @EA AND Billing BETWEEN @SB AND @EB --CallBusied Table declare @CallBusied table (Account bigint, Busied bigint) INSERT INTO @CallBusied (Account, Busied) SELECT Account, COUNT(ID) FROM mCallBusied WHERE (Account BETWEEN @SA AND @EA) AND (Billing BETWEEN @SB AND @EB) AND [Timestamp] BETWEEN @StartInfTime AND @EndInfTime GROUP BY Account --CallEnd Table declare @CallEnd table (Account bigint, Billing bigint, Client nvarchar(30), SecNoOp bigint, SecOp bigint, CheckNoOp bigint, CheckOp bigint, PatchNoOp bigint, PatchOp bigint, NonLiveNoOp bigint, NonLiveOp bigint, SecNoOpTime bigint, SecOpTime bigint, CheckNoOpTime bigint, CheckOpTime bigint, PatchNoOpTime bigint, PatchOpTime bigint, NonLiveNoOpTime bigint, NonLiveOpTime bigint, AnswerTime bigint, HoldTime bigint, CallsHeld bigint, CheckNoMsg bigint, PrcntCheckNoMsg decimal(9,3), PrcntHeld decimal(9,3), AvgHoldTime decimal(9,3), AvgAnswerTime decimal(9,3), SecTalkTime bigint, CheckTalkTime bigint, NonLiveTalkTime bigint, SecHoldTime bigint, CheckHoldTime bigint, NonLiveHoldTime bigint, SecDiscTime bigint, CheckDiscTime bigint, NonLiveDiscTime bigint, OpAssigned bigint, OpAnswered bigint, OpAbandoned bigint, AcctGroup bigint, TotalConfTime bigint, TotalConfNum bigint, TTY bigint, TTYOp bigint, TTYNoOp bigint) INSERT INTO @CallEnd (Account, Billing, Client, SecNoOp, SecOp, CheckNoOp, CheckOp,PatchNoOp, PatchOp, NonLiveNoOp, NonLiveOp, SecNoOpTime, SecOpTime, CheckNoOpTime, CheckOpTime, PatchNoOpTime, PatchOpTime, NonLiveNoOpTime, NonLiveOpTime, AnswerTime, HoldTime, CallsHeld, CheckNoMsg, PrcntCheckNoMsg, PrcntHeld, SecTalkTime, CheckTalkTime, NonLiveTalkTime, SecHoldTime, CheckHoldTime, NonLiveHoldTime, SecDiscTime, CheckDiscTime, NonLiveDiscTime, OpAssigned, OpAnswered, OpAbandoned, AcctGroup, TotalConfTime, TotalConfNum, TTY, TTYOp, TTYNoOp) SELECT Account, MAX(Billing), MAX(Client), --Count Fields(Sec, Chk, Patch, NonLive) SUM(CASE CallKind WHEN 0 THEN (CASE OpTime WHEN 0 THEN 1 ELSE 0 END) ELSE 0 END) AS SecNoOp, SUM(CASE CallKind WHEN 0 THEN(CASE OpTime WHEN 0 THEN 0 ELSE 1 END) ELSE 0 END) AS SecOp, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (CASE OpTime WHEN 0 THEN 1 ELSE 0 END) ELSE 0 END) AS CheckNoOp, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (CASE OpTime WHEN 0 THEN 0 ELSE 1 END) ELSE 0 END) AS CheckOp, SUM(CASE Ptch WHEN 0 THEN 0 ELSE (CASE OpTime WHEN 0 THEN 1 ELSE 0 END) END) AS PatchNoOp, SUM(CASE Ptch WHEN 0 THEN 0 ELSE (CASE OpTime WHEN 0 THEN 0 ELSE 1 END) END) AS PatchOp, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN 0 ELSE (CASE OpTime WHEN 0 THEN 1 ELSE 0 END) END) AS NonLiveNoOp, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN 0 ELSE (CASE OpTime WHEN 0 THEN 0 ELSE 1 END) END) AS NonLiveOp, --Time Fields(Sec, Chk, Patch, NonLive) SUM(CASE CallKind WHEN 0 THEN (CASE WHEN (@NoOpAhold + @NoOpDisc + @NoOpGone + @NoOpHold + @NoOpPatch + @NoOpRing + @NoOpWait + @NoOpZero) = 0 THEN (Duration - OpTime) ELSE ((CASE WHEN @NoOpAhold > 0 THEN (Ahold - HiAhold) ELSE 0 END) + (CASE WHEN @NoOpDisc > 0 THEN (Disc - HiDisc) ELSE 0 END) + (CASE WHEN @NoOpGone > 0 THEN (Gone - HiGone) ELSE 0 END) + (CASE WHEN @NoOpHold > 0 THEN (Hold - HiHold) ELSE 0 END) + (CASE WHEN @NoOpPatch > 0 THEN (Ptch - HiPtch) ELSE 0 END) + (CASE WHEN @NoOpRing > 0 THEN (Ring - HiRing) ELSE 0 END) + (CASE WHEN @NoOpWait > 0 THEN (Wait - HiWait) ELSE 0 END) + (CASE WHEN @NoOpZero > 0 THEN (Zero - HiZero) ELSE 0 END)) END) ELSE 0 END) AS SecNoOpTime, SUM(CASE CallKind WHEN 0 THEN(CASE OpTime WHEN 0 THEN 0 ELSE OpTime END) ELSE 0 END) AS SecOpTime, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (CASE WHEN (@NoOpAhold + @NoOpDisc + @NoOpGone + @NoOpHold + @NoOpPatch + @NoOpRing + @NoOpWait + @NoOpZero) = 0 THEN (Duration - OpTime) ELSE ((CASE WHEN @NoOpAhold > 0 THEN (Ahold - HiAhold) ELSE 0 END) + (CASE WHEN @NoOpDisc > 0 THEN (Disc - HiDisc) ELSE 0 END) + (CASE WHEN @NoOpGone > 0 THEN (Gone - HiGone) ELSE 0 END) + (CASE WHEN @NoOpHold > 0 THEN (Hold - HiHold) ELSE 0 END) + (CASE WHEN @NoOpPatch > 0 THEN (Ptch - HiPtch) ELSE 0 END) + (CASE WHEN @NoOpRing > 0 THEN (Ring - HiRing) ELSE 0 END) + (CASE WHEN @NoOpWait > 0 THEN (Wait - HiWait) ELSE 0 END) + (CASE WHEN @NoOpZero > 0 THEN (Zero - HiZero) ELSE 0 END)) END) ELSE 0 END) AS CheckNoOpTime, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (CASE OpTime WHEN 0 THEN 0 ELSE OpTime END) ELSE 0 END) AS CheckOpTime, SUM(CASE Ptch WHEN 0 THEN 0 ELSE (CASE OpTime WHEN 0 THEN Ptch ELSE (CASE @NewPatch WHEN 0 THEN 0