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