August 7, 2009 at 1:48 am
Hi there,
I have a problem that i am unable to figure it out and need help please. I recorded a network traffic and direct the data to a SQL Server database using Stored Procedure below. However, the data get into the table with different number of records - never the same. Some time 200, 250, 270 and etc. I tried WITH (NOLOCK), WITH(UPDLOCK) or without anything. But result of records is never the same. Can anyone guide me please?
Regards,
Kai
CREATE PROCEDURE [dbo].[usp_SummarizeACDQueue]
@TempASAIDataSwitch as char(1) = 'A',
@SiteId int = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tmpRawQueue TABLE(
Idx int,
ACDGroupId int,
CallId int,
InQDate smalldatetime
);
if @TempASAIDataSwitch = 'A'
begin
--Insert new call to the queue
INSERT INTO @tmpRawQueue
SELECT max(TempCalldataId) as Idx
, DomainExtension
, CallId
, DateReceived
FROM TT_ASAIDataA WITH (NOLOCK)
WHERE DomainExtension > 0 and [Count] > 0
and CallId not IN (SELECT CallId FROM TT_ASAIDataA WHERE OperationValue = 'Call Ended')
GROUP BY DomainExtension , callid, DateReceived
ORDER BY idx
--Update Call Ended status: Out Of Queue for previous event
UPDATE [TS_ACDCallInQueue]
SET [InQueueFlag] = 0
,[DateModified] = GETDATE()
FROM (SELECT CallId FROM TT_ASAIDataA WHERE OperationValue = 'Call Ended') tmp
WHERE [TS_ACDCallInQueue].CallId = tmp.CallId
end
if @TempASAIDataSwitch = 'B'
begin
--Insert new call to the queue
INSERT INTO @tmpRawQueue
SELECT max(TempCalldataId) as Idx
, DomainExtension
, CallId
, DateReceived
FROM TT_ASAIDataB WITH (NOLOCK)
WHERE DomainExtension > 0 and [Count] > 0
and CallId not IN (SELECT CallId FROM TT_ASAIDataB WHERE OperationValue = 'Call Ended')
GROUP BY DomainExtension , callid, DateReceived
ORDER BY idx
--Update Call Ended status: Out Of Queue for previous event
UPDATE [TS_ACDCallInQueue]
SET [InQueueFlag] = 0
,[DateModified] = GETDATE()
FROM (SELECT CallId FROM TT_ASAIDataB WHERE OperationValue = 'Call Ended') tmp
WHERE [TS_ACDCallInQueue].CallId = tmp.CallId
end
/**************************************/
--Insert new CallId to the Queue
INSERT INTO [TS_ACDCallInQueue]
([LSCallId]
,[ACDGroupId]
,[CallId]
,[InQueueTime]
,[InQueueFlag]
,[SiteId]
,[Deleted])
SELECT Idx
, ACDGroupId
, CallId
, InQDate
, 1 -- InQFlag
, 1 --@SiteId
, 0 --deltete
FROM @tmpRawQueue
END
August 7, 2009 at 8:33 am
(sorry)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply