Record count is never the same

• kreecha_pu

  SSC Veteran

  Points: 251

  More actions

  August 7, 2009 at 1:48 am

  #134573

  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

• ManicStar

  SSCoach

  Points: 17992

  More actions

  August 7, 2009 at 8:33 am

  #1036037

  (sorry)