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