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 ELSE (Ptch - HiPtch) END) END) END) AS PatchNoOpTime, SUM(CASE Ptch WHEN 0 THEN 0 ELSE (CASE OpTime WHEN 0 THEN 0 ELSE (CASE @NewPatch WHEN 0 THEN Ptch ELSE (HiPtch + HiConf) END) END) END) AS PatchOpTime, SUM(CASE WHEN CallKind > 1 AND 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 NonLiveNoOpTime, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN 0 ELSE (CASE OpTime WHEN 0 THEN 0 ELSE OpTime END) END) AS NonLiveOpTime, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN (CASE WHEN (@AnswerRing + @AnswerAhold + @AnswerZero) > 0 THEN ((CASE WHEN @AnswerRing = 1 THEN Ring ELSE 0 END) + (CASE WHEN @AnswerAhold = 1 THEN Ahold ELSE 0 END) + (CASE WHEN @AnswerZero = 1 THEN Zero ELSE 0 END)) ELSE 0 END) ELSE 0 END) AS AnswerTime, SUM(CASE WHEN ((CallKind < 2 OR CallKind = 16) AND OpTime > 0) THEN Hold ELSE 0 END) AS HoldTime, SUM(CASE WHEN ((CallKind < 2 OR CallKind = 16) AND OpTime > 0) THEN (CASE Hold WHEN 0 THEN 0 ELSE 1 END) ELSE 0 END) AS CallsHeld, SUM(CASE WHEN (CallKind = 1 OR CallKind = 16) AND (NoMessage > 0) THEN 1 ELSE 0 END) as CheckNoMsg, CAST(SUM(CASE WHEN CallKind = 0 OR CallKind = 1 OR CallKind = 16 THEN (CASE OpTime WHEN 0 THEN 0 ELSE 1 END) ELSE 0 END) AS DECIMAL(9,3)) AS PrcntCheckNoMsg, CAST(SUM(CASE WHEN CallKind = 0 OR CallKInd = 1 OR CallKind = 16 THEN (CASE OpTime WHEN 0 THEN 0 ELSE 1 END) ELSE 0 END) AS DECIMAL(9,3)) AS PrcntHeld, --Talk Time SUM(CASE CallKind WHEN 0 THEN (CASE WHEN @ParadoxTalk = 1 THEN (HiTalk + HiTk1 + HiTk2 + HiConf) ELSE HiTalk END) ELSE 0 END) as SecTalkTime, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (CASE WHEN @ParadoxTalk = 1 THEN (HiTalk + HiTk1 + HiTk2 + HiConf) ELSE HiTalk END) ELSE 0 END) as CheckTalkTime, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN 0 ELSE (CASE WHEN @ParadoxTalk = 1 THEN (HiTalk + HiTk1 + HiTk2 + HiConf) ELSE HiTalk END) END) as NonLiveTalkTime, ---Hold Time SUM(CASE CallKind WHEN 0 THEN (HiHold) ELSE 0 END) as SecHoldTime, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (HiHold) ELSE 0 END) as CheckHoldTime, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN 0 ELSE (HiHold) END) as NonLiveHoldTime, --Disc Time SUM(CASE CallKind WHEN 0 THEN (HiDisc) ELSE 0 END) as SecDiscTime, SUM(CASE WHEN CallKind = 1 OR CallKind = 16 THEN (HiDisc) ELSE 0 END) as CheckDiscTime, SUM(CASE WHEN CallKind < 2 OR CallKind = 16 THEN 0 ELSE (HiDisc) END) as NonLiveDiscTime, SUM(CASE Assigned WHEN 0 THEN 0 ELSE 1 END) as OpAssigned, SUM(CASE Answered WHEN 0 THEN 0 ELSE 1 END) as OpAnswered, SUM(CASE Abandoned WHEN 0 THEN 0 ELSE (CASE Ptch WHEN 0 THEN 1 ELSE 0 END) END) as OpAbandoned, MAX(AcctGroup) AS AcctGroup, --ConfBridge Stats SUM(CASE VConf WHEN 0 THEN 0 ELSE Duration END) as TotalConfTime, SUM(CASE VConf WHEN 0 THEN 0 ELSE 1 END) as TotalConfNum, --TTY SUM(CASE TTY WHEN 0 THEN 0 ELSE 1 END) as TTY, SUM(CASE TTY WHEN 0 THEN 0 ELSE OpTime END) as TTYOp, SUM(CASE TTY WHEN 0 THEN 0 ELSE (CASE WHEN (Duration - OpTime) < 0 THEN 0 ELSE (Duration - OpTime) END) END) as TTYNoOp FROM mCallEnd WHERE (Account BETWEEN @SA AND @EA) AND (Billing BETWEEN @SB AND @EB) AND ([Timestamp] BETWEEN @StartInfTime AND @EndInfTime) AND (Xdial = 0 OR TTY > 0) GROUP BY Account --CallEndPBX Table declare @CallEndPBX table (Account bigint, DialoutPBX bigint, DialoutPBXTime bigint, PatchPBX bigint, PatchPBXTime bigint) INSERT INTO @CallEndPBX (Account, DialoutPBX, DialoutPBXTime, PatchPBX, PatchPBXTime) SELECT Account, --DialoutPBX SUM(CASE XDial WHEN 0 THEN 0 ELSE (CASE TTY WHEN 0 THEN 1 ELSE 0 END) END) as DialoutPBX, SUM(CASE XDial WHEN 0 THEN 0 ELSE (CASE TTY WHEN 0 THEN Duration ELSE 0 END) END) as DialoutPBXTime, --PatchPBX SUM(CASE XDial WHEN 0 THEN 0 ELSE (CASE TTY WHEN 0 THEN (CASE Ptch WHEN 0 THEN 0 ELSE 1 END) ELSE 0 END) END) as PatchPBX, SUM(CASE XDial WHEN 0 THEN 0 ELSE (CASE TTY WHEN 0 THEN (CASE Ptch WHEN 0 THEN 0 ELSE Duration END) ELSE 0 END) END) as PatchPBXTime FROM mCallEnd WHERE (Account BETWEEN @SA AND @EA) AND (Billing BETWEEN @SB AND @EB) AND ([Timestamp] BETWEEN @StartInfTime AND @EndInfTime) AND (Xdial > 0 AND TTY = 0) GROUP BY Account --ClientMaint Table declare @ClientMaint table (Account bigint, SuperTime bigint) INSERT INTO @ClientMaint (Account, SuperTime) SELECT Account, ISNULL(SUM(SuperTime), 0) AS SuperTime FROM mClientMaint WHERE (Account BETWEEN @SA AND @EA) AND (Billing BETWEEN @SB AND @EB) AND ([Timestamp] BETWEEN @StartInfTime AND @EndInfTime) AND ((@ClientMaintChange = 1 AND Changed > 0 ) OR (@ClientMaintChange = 0)) GROUP BY Account --ConfBridge Table declare @ConfBridge table (Account bigint, TotalConfPartTime bigint, TotalConfPartNum bigint) INSERT INTO @ConfBridge (Account, TotalConfPartTime, TotalConfPartNum) SELECT Account, SUM(Duration) as TotalConfPartTime, Count(ID) as TotalConfPartNum FROM mConfBridge WHERE (Account BETWEEN @SA AND @EA) AND (Billing BETWEEN @SB AND @EB) AND [Timestamp] BETWEEN @StartInfTime AND @EndInfTime GROUP BY Account --Dialout Table declare @Dialout table (Account bigint, DialoutNoOp bigint, DialoutOp bigint, DialoutNoOpTime bigint, DialoutOpTime bigint) INSERT INTO @Dialout (Account, DialoutNoOp, DialoutOp, DialoutNoOpTime, DialoutOpTime) SELECT Account, SUM(CASE LTRIM(RTRIM(ISNULL(Initials, ''))) WHEN '' THEN 1 WHEN 'aut' THEN 1 WHEN 'chk' THEN 1 WHEN 'vm' THEN 1 ELSE 0 END) as DialoutNoOp, SUM(CASE LTRIM(RTRIM(ISNULL(Initials, ''))) WHEN '' THEN 0 WHEN 'aut' THEN 0 WHEN 'chk' THEN 0 WHEN 'vm' THEN 0 ELSE 1 END) as DialoutOp, SUM(CASE LTRIM(RTRIM(ISNULL(Initials, ''))) WHEN '' THEN Duration WHEN 'aut' THEN Duration WHEN 'chk' THEN Duration WHEN 'vm' THEN Duration ELSE 0 END) as DialoutNoOpTime, SUM(CASE LTRIM(RTRIM(ISNULL(Initials, ''))) WHEN '' THEN 0 WHEN 'aut' THEN 0 WHEN 'chk' THEN 0 WHEN 'vm' THEN 0 ELSE Duration END) as DialoutOpTime FROM mDial WHERE (Account BETWEEN @SA AND @EA) AND (Billing BETWEEN @SB AND @EB) AND ([T