While Loop Vs FAST_FORWARD READ_ONLY CURSOR

Question

Post reply

While Loop Vs FAST_FORWARD READ_ONLY CURSOR

jwalin

Mr or Mrs. 500

Points: 599
More actions
May 8, 2009 at 11:53 am

#204763

Hi
Here I am posting my 2 store procedure 1 is using While loop and another using Cursor
WHILE LOOP
=====================
ALTER PROC CursorSimulator_Test
(
@startDate datetime,
@endDate datetime
)
AS
--SELECT DateTime,SkillTargetID,PeripheralID,sum(HandledCallsTalkTimeToHalf),count(*)
--FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
--WHERE SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
-- AND datetime >= @startDate and datetime < @endDate
--GROUP BY DateTime,SkillTargetID,PeripheralID
--Order By SkillTargetID,DateTime
/*Prepare TABLE variable to take resultset*/
DECLARE @tbl TABLE
(
RowID INT IDENTITY(1, 1),
CallDateTime datetime,
SkillTargetID int,
PeripheralID int,
HandledCallsTalkTimeToHalf int,
CountCalls int
)
/*Local variables */
DECLARE @date datetime,
@IPCCAgentTargetId int,
@PeripheralId int,
@AgntTotalTalkTime int,
@noAgentSG int,
@count int, /*create local @@fetch_status*/
@iRow int /*row pointer (index)*/
DECLARE @AgntAvailTimeOnDefaultSk int,
@sumR21 numeric (11,5),
@AfterSplitSum int,
@ErrCorrSkillId int,
@CntIFUpdate int,
@CntELSEUpdate int
DECLARE @CntIfUpdate1 int,
@CntElseUpdate1 int
SET @CntIFUpdate = 0
SET @CntELSEUpdate = 0
SET @CntIfUpdate1 = 0
SET @CntElseUpdate1 = 0
/* create array simulator */
INSERT @tbl
SELECT [DateTime],SkillTargetID,PeripheralID,sum(HandledCallsTalkTimeToHalf),count(*)
FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
WHERE SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
AND [datetime] >= @startDate and [datetime] < @endDate
GROUP BY [DateTime],SkillTargetID,PeripheralID
--Having count(*) > 1
Order By SkillTargetID,[DateTime]
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
print 'Counts = ' + Convert(varchar, @Count)
/*create Temparary Tables*/
SELECT 0 SkillGroupSkillTargetID,0 SkillTargetID,100.000000 R2,1000.00000 R21 INTO #Agent_Temp
/*initialize index counter*/
SET @iRow = 1
print 'START Loop'
print getDate()
print '====================================='
/*establish loop structure*/
WHILE @iRow <= @count
BEGIN
/*get row values*/
SELECT @date = CallDateTime, @IPCCAgentTargetId = SkillTargetID,
@PeripheralId = PeripheralID, @AgntTotalTalkTime = HandledCallsTalkTimeToHalf,
@noAgentSG = CountCalls
FROM @tbl
WHERE RowID = @iRow
--print Convert(varchar, @date)
--print convert(varchar, @IPCCAgentTargetId)
--print convert(varchar, @PeripheralId)
--print convert(varchar, @AgntTotalTalkTime)
--print convert(varchar, @noAgentSG)
--print '============================='
-- get the Agent Available Time for Default Skill Group
SELECT @AgntAvailTimeOnDefaultSk = AvailTimeToHalf FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
WHERE DateTime=@date and SkillTargetID=@IPCCAgentTargetId and PeripheralId=@PeripheralId
and SkillGroupSkillTargetID =
(
Select
Case @PeripheralId
When 5000 Then 7820
When 5006 Then 10023
When 5016 Then 13948
When 5024 Then 16368
End
)
TRUNCATE TABLE #Agent_Temp
IF @noAgentSG > 1
BEGIN
IF (@AgntAvailTimeOnDefaultSk <> 0) --OR (@AgntAvailTimeOnDefaultSk IS NOT NULL)
BEGIN
IF @AgntTotalTalkTime=0
SET @AgntTotalTalkTime=1
INSERT INTO #Agent_Temp (SkillGroupSkillTargetID,SkillTargetID,R21,R2)
SELECT SkillGroupSkillTargetID, SkillTargetID,
(AvailTimeToHalf*1.000)/@AgntAvailTimeOnDefaultSk, 0
FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
WHERE SkillTargetId=@IPCCAgentTargetId
AND [Datetime] = @date AND PeripheralId=@PeripheralId
AND SkillGroupSkillTargetID NOT IN (7820,10023,10793,13948,16368)
SELECT @sumR21 = sum(R21) FROM #Agent_Temp
IF @sumR21 = 0.00
SELECT @sumR21=count(*) FROM #Agent_Temp
UPDATE #Agent_Temp SET R2=R21/@sumR21
IF @AgntTotalTalkTime<>1
BEGIN
SET @CntIfUpdate1 = @CntIfUpdate1 + 1
END
ELSE
BEGIN
SET @CntElseUpdate1 = @CntElseUpdate1 + 1
END
SELECT @AfterSplitSum = SUM(wcAvailableTime) FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
WHERE
[DateTime]=@date AND SkillTargetID=@IPCCAgentTargetId
AND SkillGroupSkillTargetID NOT IN (7820,10023,10793,13948,16368)
If @AfterSplitSum<>@AgntAvailTimeOnDefaultSk
BEGIN
SELECT @ErrCorrSkillId = SkillGroupSkillTargetID FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
WHERE SkillTargetID=@IPCCAgentTargetId and [DateTime]=@date
AND SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
ORDER BY Ratio1 DESC
SET @CntIfUpdate = @CntIfUpdate + 1
END
END -- END OF (@AgntAvailTimeOnDefaultSk <> 0) OR (@AgntAvailTimeOnDefaultSk IS NOT NULL)
END -- END OF (@noAgentSG > 1)
ELSE
BEGIN
SET @CntELSEUpdate = @CntELSEUpdate + 1
END
/*go to next row*/
SET @iRow = @iRow + 1
END-- Main END OF WHILE
print '====================================='
--print Convert( Varchar, @CntIfUpdate1)
--print Convert( Varchar, @CntELSEUpdate1)
--
--
--print Convert( Varchar, @CntIfUpdate)
--print Convert( Varchar, @CntELSEUpdate)
print Convert(varchar,@iRow)
select @CntIfUpdate1, @CntELSEUpdate1, @CntIfUpdate, @CntELSEUpdate
print '====================================='
print 'END Loop'
print GetDate()
/*UPDATE records where only */
GO
====================================
***************************************************************
FAST_FORWARD READ_ONLY CURSOR
***************************************************************
ALTER PROCEDURE [dbo].[WithCursor_Test]
@startDate datetime , @endDate datetime
as
set nocount on
Declare Agent_SkillGroup Cursor FAST_FORWARD READ_ONLY FOR
SELECT DateTime,SkillTargetID,PeripheralID,sum(HandledCallsTalkTimeToHalf),count(*)
FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
--This is to be verified
WHERE SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
--and DateTime='05/02/2005 14:00:00'
--and SkillTargetId=9048
AND datetime >= @startDate and datetime < @endDate
GROUP BY DateTime,SkillTargetID,PeripheralID
Order By SkillTargetID,DateTime
Declare @date datetime
Declare @IPCCAgentTargetId int
Declare @AgntTotalTalkTime int
Declare @PeripheralId int
Declare @AfterSplitSum int
Declare @AgntAvailTimeOnDefaultSk int
Declare @sumR21 numeric (11,5)
Declare @ErrCorrSkillId int
Declare @noAgentSG int
Declare @CntIFUpdate int ,
@CntELSEUpdate int
DECLARE @CntIfUpdate1 int,
@CntElseUpdate1 int
SET @CntIFUpdate = 0
SET @CntELSEUpdate = 0
SET @CntIfUpdate1 = 0
SET @CntElseUpdate1 = 0
--if object_id('tempdb..#Agent_Temp')>0 Drop table #Agent_Temp
select 0 SkillGroupSkillTargetID,0 SkillTargetID,100.000000 R2,1000.00000 R21
into #Agent_Temp
print 'START Loop'
print getDate()
print '====================================='
Declare @iRow int
SET @iRow = 0
OPEN Agent_SkillGroup
fetch next from Agent_SkillGroup into @date,@IPCCAgentTargetId,@PeripheralId,@AgntTotalTalkTime,@noAgentSG
WHILE @@FETCH_STATUS <>-1
BEGIN
--if the agent has no talk time then the wait time will be equally distributed
--among all skill groups(excluding default)
Set @iRow = @iRow + 1
--
--print Convert(varchar, @date)
--print convert(varchar, @IPCCAgentTargetId)
--print convert(varchar, @PeripheralId)
--print convert(varchar, @AgntTotalTalkTime)
--print convert(varchar, @noAgentSG)
--print '============================='
SELECT @AgntAvailTimeOnDefaultSk = AvailTimeToHalf FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
WHERE DateTime=@date and SkillTargetID=@IPCCAgentTargetId and PeripheralId=@PeripheralId
and SkillGroupSkillTargetID = (
Select Case @PeripheralId
When 5000 Then 7820
When 5006 Then 10023
When 5016 Then 13948
When 5024 Then 16368
End
)
Truncate table #Agent_Temp
if @noAgentSG>1
Begin
if @AgntAvailTimeOnDefaultSk <>0 ---No Available Time
Begin
If @AgntTotalTalkTime=0 Select @AgntTotalTalkTime=1
insert into #Agent_Temp (SkillGroupSkillTargetID,SkillTargetID,R21,R2)
Select SkillGroupSkillTargetID,SkillTargetID , AvailTimeToHalf*1.000/ @AgntAvailTimeOnDefaultSk,0
from WCI_IP_ST_AGENT_SKILL_GROUP_HH
where SkillTargetId=@IPCCAgentTargetId
and Datetime = @date and PeripheralId=@PeripheralId
and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
Select @sumR21 = sum(R21) from #Agent_Temp
If @sumR21 = 0.00 Select @sumR21=count(*) From #Agent_Temp
Update #Agent_Temp Set R2=R21/@sumR21
If @AgntTotalTalkTime<>1
SET @CntIfUpdate1 = @CntIfUpdate1 + 1
Else
SET @CntElseUpdate1 = @CntElseUpdate1 + 1
----Rounding correction
Select @AfterSplitSum = sum(wcAvailableTime) from WCI_IP_ST_AGENT_SKILL_GROUP_HH
Where
DateTime=@date and SkillTargetID=@IPCCAgentTargetId
and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
If @AfterSplitSum<>@AgntAvailTimeOnDefaultSk
Begin
Select @ErrCorrSkillId = SkillGroupSkillTargetID FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH
where SkillTargetID=@IPCCAgentTargetId and DateTime=@date
and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)
Order by Ratio1 desc
SET @CntIfUpdate = @CntIfUpdate + 1
End ------ End Rounding correction
End --end @AgntAvailTimeOnDefaultSk <>0 true
End --end @noAgentSG>1 true
Else
SET @CntELSEUpdate = @CntELSEUpdate + 1
fetch next from Agent_SkillGroup into @date,@IPCCAgentTargetId,@PeripheralId,@AgntTotalTalkTime,@noAgentSG
END
CLOSE Agent_SkillGroup
DEALLOCATE Agent_SkillGroup
print '====================================='
print Convert(varchar,@iRow)
--print Convert( Varchar, @CntIfUpdate1)
--print Convert( Varchar, @CntELSEUpdate1)
--
--
--print Convert( Varchar, @CntIfUpdate)
--print Convert( Varchar, @CntELSEUpdate)
select @CntIfUpdate1, @CntELSEUpdate1, @CntIfUpdate, @CntELSEUpdate
print '====================================='
print 'END Loop'
print GetDate()
======================================================================
COMPARISON RESULTS
-----------------------
NO. OF RECORDS WHILE LOOP CURSOR
13588 54 SECS. 14 SECS
39427 5 MINS. 29 SECS 39 SECS
Where ever I am reading about the cursor everbody said avoid the cursor to use it is very resource consumption and slow. But If you compare my result it shows that cursor is faster then while loop.
Please advise me it is safe to use the cursor or not?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

That kind of cursor is generally going to be faster than a While loop. In this case, you've already shown that it is.

The point of not using cursors, however, is to not use loops at all.

It looks to me like this process could be done with a temp table and a query joining to it. That will almost certainly be better than either of these loop solutions.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jwalin Mr or Mrs. 500 Points: 599 More actions · Answer 2

thanks for quick reply. Could you please explain more about temp table and a query joining to it?

Is this process possible without Cursor or while loop?

Thanks

jwalin

GSquared SSC Guru Points: 260824 More actions · Answer 3

It probably can be done without a loop/cursor. I'd need create scripts for tables and insert statements for some sample data, to tell for sure. But it does look that way so far.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jwalin Mr or Mrs. 500 Points: 599 More actions · Answer 4

/****** Object: Table [dbo].[WCI_IP_ST_AGENT_SKILL_GROUP_HH] Script Date: 05/08/2009 14:28:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[WCI_IP_ST_AGENT_SKILL_GROUP_HH](

[DateTime] [datetime] NULL,

[EmpNumber] [varchar](32) NOT NULL,

[Skill Group] [varchar](32) NOT NULL,

[SkillGroupSkillTargetID] [int] NOT NULL,

[SkillTargetID] [int] NOT NULL,

[wcEmployeeId] [int] NOT NULL,

[wcTaskId] [int] NOT NULL,

[PeripheralID] [int] NOT NULL,

[wcCallsAnswered] [int] NULL,

[wcTalkTime] [int] NULL,

[wcAvailableTime] [int] NOT NULL,

[wcWrapUp] [int] NOT NULL,

[AbandonRingCallsToHalf] [int] NULL,

[AbandonRingTimeToHalf] [int] NULL,

[AbandonHoldCallsToHalf] [int] NULL,

[AgentOutCallsTimeToHalf] [int] NULL,

[AgentOutCallsTalkTimeToHalf] [int] NULL,

[AgentOutCallsToHalf] [int] NULL,

[AgentOutCallsOnHoldToHalf] [int] NULL,

[AgentOutCallsOnHoldTimeToHalf] [int] NULL,

[AgentTerminatedCallsToHalf] [int] NULL,

[AnswerWaitTimeToHalf] [int] NULL,

[AvailTimeToHalf] [int] NULL,

[BusyOtherTimeToHalf] [int] NULL,

[CallbackMessagesTimeToHalf] [int] NULL,

[CallbackMessagesToHalf] [int] NULL,

[CallsAnsweredToHalf] [int] NULL,

[CallsHandledToHalf] [int] NULL,

[ConsultativeCallsToHalf] [int] NULL,

[ConsultativeCallsTimeToHalf] [int] NULL,

[ConferencedInCallsToHalf] [int] NULL,

[ConferencedInCallsTimeToHalf] [int] NULL,

[ConferencedOutCallsToHalf] [int] NULL,

[ConferencedOutCallsTimeToHalf] [int] NULL,

[HandledCallsTalkTimeToHalf] [int] NULL,

[HandledCallsTimeToHalf] [int] NULL,

[HoldTimeToHalf] [int] NULL,

[IncomingCallsOnHoldTimeToHalf] [int] NULL,

[IncomingCallsOnHoldToHalf] [int] NULL,

[InternalCallsOnHoldTimeToHalf] [int] NULL,

[InternalCallsOnHoldToHalf] [int] NULL,

[InternalCallsRcvdTimeToHalf] [int] NULL,

[InternalCallsRcvdToHalf] [int] NULL,

[InternalCallsTimeToHalf] [int] NULL,

[InternalCallsToHalf] [int] NULL,

[LoggedOnTimeToHalf] [int] NULL,

[NotReadyTimeToHalf] [int] NULL,

[RedirectNoAnsCallsToHalf] [int] NULL,

[RedirectNoAnsCallsTimeToHalf] [int] NULL,

[ReservedStateTimeToHalf] [int] NULL,

[ShortCallsToHalf] [int] NULL,

[SupervAssistCallsTimeToHalf] [int] NULL,

[SupervAssistCallsToHalf] [int] NULL,

[TalkInTimeToHalf] [int] NULL,

[TalkOtherTimeToHalf] [int] NULL,

[TalkOutTimeToHalf] [int] NULL,

[TransferredInCallsTimeToHalf] [int] NULL,

[TransferredInCallsToHalf] [int] NULL,

[TransferredOutCallsToHalf] [int] NULL,

[WorkNotReadyTimeToHalf] [int] NULL,

[WorkReadyTimeToHalf] [int] NULL,

[AutoOutCallsToHalf] [int] NULL,

[AutoOutCallsTimeToHalf] [int] NULL,

[AutoOutCallsTalkTimeToHalf] [int] NULL,

[AutoOutCallsOnHoldToHalf] [int] NULL,

[AutoOutCallsOnHoldTimeToHalf] [int] NULL,

[PreviewCallsToHalf] [int] NULL,

[PreviewCallsTimeToHalf] [int] NULL,

[PreviewCallsTalkTimeToHalf] [int] NULL,

[PreviewCallsOnHoldToHalf] [int] NULL,

[PreviewCallsOnHoldTimeToHalf] [int] NULL,

[ReserveCallsToHalf] [int] NULL,

[ReserveCallsTimeToHalf] [int] NULL,

[ReserveCallsTalkTimeToHalf] [int] NULL,

[ReserveCallsOnHoldToHalf] [int] NULL,

[ReserveCallsOnHoldTimeToHalf] [int] NULL,

[TalkAutoOutTimeToHalf] [int] NULL,

[TalkPreviewTimeToHalf] [int] NULL,

[TalkReserveTimeToHalf] [int] NULL,

[BargeInCallsToHalf] [int] NULL,

[InterceptCallsToHalf] [int] NULL,

[MonitorCallsToHalf] [int] NULL,

[WhisperCallsToHalf] [int] NULL,

[EmergencyAssistsToHalf] [int] NULL,

[InterruptedTimeToHalf] [int] NULL,

[Ratio1] [decimal](9, 6) NULL CONSTRAINT [DF_WCI_IP_ST_AGENT_SKILL_GROUP_HH_Ratio1] DEFAULT (0),

[Ratio2] [decimal](9, 6) NULL CONSTRAINT [DF_WCI_IP_ST_AGENT_SKILL_GROUP_HH_Ratio2] DEFAULT (0)

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

==============================

I am also attchaing .Zip file for some samples records

Thank you

jwalin

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

I was going to convert the procedure for you, but after reading through it, it does not appear to do anything. Could you please expplain what these procedures are supposed to do? What is their output or effect supposed to be?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

jwalin Mr or Mrs. 500 Points: 599 More actions · Answer 6

Here I am giving you my actual store procedure ... Thank you for looking into it.

=======================

Declare Agent_SkillGroup Cursor FAST_FORWARD READ_ONLY FOR

SELECT DateTime,SkillTargetID,PeripheralID,sum(HandledCallsTalkTimeToHalf),count(*)

FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH

WHERE SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

GROUP BY DateTime,SkillTargetID,PeripheralID

Order By SkillTargetID,DateTime

Declare @date datetime

Declare @IPCCAgentTargetId int

Declare @AgntTotalTalkTime int

Declare @PeripheralId int

Declare @AfterSplitSum int

Declare @AgntAvailTimeOnDefaultSk int

Declare @sumR21 numeric (11,5)

Declare @ErrCorrSkillId int

Declare @noAgentSG int

if object_id('tempdb..#Agent_Temp')>0 Drop table #Agent_Temp

select 0 SkillGroupSkillTargetID,0 SkillTargetID,100.000000 R2,1000.00000 R21

into #Agent_Temp

OPEN Agent_SkillGroup

fetch next from Agent_SkillGroup into @date,@IPCCAgentTargetId,@PeripheralId,@AgntTotalTalkTime,@noAgentSG

WHILE @@FETCH_STATUS -1

BEGIN

SELECT @AgntAvailTimeOnDefaultSk = AvailTimeToHalf FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH

WHERE DateTime=@date and SkillTargetID=@IPCCAgentTargetId and PeripheralId=@PeripheralId

and SkillGroupSkillTargetID = (

Select Case @PeripheralId

When 5000 Then 7820

When 5006 Then 10023

When 5016 Then 13948

When 5024 Then 16368

End

)

Truncate table #Agent_Temp

if @noAgentSG>1

Begin

if @AgntAvailTimeOnDefaultSk 0 ---No Available Time

Begin

If @AgntTotalTalkTime=0 Select @AgntTotalTalkTime=1

insert into #Agent_Temp (SkillGroupSkillTargetID,SkillTargetID,R21,R2)

Select SkillGroupSkillTargetID,SkillTargetID , AvailTimeToHalf*1.000/ @AgntAvailTimeOnDefaultSk,0

from WCI_IP_ST_AGENT_SKILL_GROUP_HH

where SkillTargetId=@IPCCAgentTargetId

and Datetime = @date and PeripheralId=@PeripheralId

and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

Select @sumR21 = sum(R21) from #Agent_Temp

If @sumR21 = 0.00 Select @sumR21=count(*) From #Agent_Temp

Update #Agent_Temp Set R2=R21/@sumR21

If @AgntTotalTalkTime1

update WCI_IP_ST_AGENT_SKILL_GROUP_HH set

Ratio1=HandledCallsTalkTimeToHalf*1.000/@AgntTotalTalkTime,

Ratio2=R2,

wcAvailableTime = convert (int,@AgntAvailTimeOnDefaultSk*1.0000 * ((HandledCallsTalkTimeToHalf*1.000/@AgntTotalTalkTime)+R2)/2)

From #Agent_Temp

where WCI_IP_ST_AGENT_SKILL_GROUP_HH.DateTime=@date and WCI_IP_ST_AGENT_SKILL_GROUP_HH.SkillTargetID=@IPCCAgentTargetId

and WCI_IP_ST_AGENT_SKILL_GROUP_HH.SkillGroupSkillTargetID=#Agent_Temp.SkillGroupSkillTargetID

and WCI_IP_ST_AGENT_SKILL_GROUP_HH.SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

Else

update WCI_IP_ST_AGENT_SKILL_GROUP_HH set

Ratio1=1.000/@noAgentSG,

Ratio2=R2,

wcAvailableTime = convert (int,@AgntAvailTimeOnDefaultSk*1.0000 * ((1.000/@noAgentSG)+R2)/2)

From #Agent_Temp

where WCI_IP_ST_AGENT_SKILL_GROUP_HH.DateTime=@date and WCI_IP_ST_AGENT_SKILL_GROUP_HH.SkillTargetID=@IPCCAgentTargetId

and WCI_IP_ST_AGENT_SKILL_GROUP_HH.SkillGroupSkillTargetID=#Agent_Temp.SkillGroupSkillTargetID

and WCI_IP_ST_AGENT_SKILL_GROUP_HH.SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

----Rounding correction

Select @AfterSplitSum = sum(wcAvailableTime) from WCI_IP_ST_AGENT_SKILL_GROUP_HH

Where

DateTime=@date and SkillTargetID=@IPCCAgentTargetId

and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

If @AfterSplitSum@AgntAvailTimeOnDefaultSk

Begin

Select @ErrCorrSkillId = SkillGroupSkillTargetID FROM WCI_IP_ST_AGENT_SKILL_GROUP_HH

where SkillTargetID=@IPCCAgentTargetId and DateTime=@date

and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

Order by Ratio1 desc

Update WCI_IP_ST_AGENT_SKILL_GROUP_HH set

wcAvailableTime= wcAvailableTime +(@AgntAvailTimeOnDefaultSk-@AfterSplitSum)

where DateTime=@date and SkillTargetID=@IPCCAgentTargetId

and SkillGroupSkillTargetID = @ErrCorrSkillId

End

Else

Update WCI_IP_ST_AGENT_SKILL_GROUP_HH set

wcAvailableTime= @AgntAvailTimeOnDefaultSk

where DateTime=@date and SkillTargetID=@IPCCAgentTargetId

and SkillGroupSkillTargetID not in (7820,10023,10793,13948,16368)

fetch next from Agent_SkillGroup into @date,@IPCCAgentTargetId,@PeripheralId,@AgntTotalTalkTime,@noAgentSG

END

CLOSE Agent_SkillGroup

DEALLOCATE Agent_SkillGroup

print getdate()

GSquared SSC Guru Points: 260824 More actions · Answer 7

It looks like what this does is take each Skill Group and Skill Group Target, grouped by Peripheral and Date, and update the available time by subtracting the Total Time.

Am I reading it correctly?

Edit: Actually, it's not by date, it's by exact date and time, and looks like it's broken into half-hour increments.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jwalin Mr or Mrs. 500 Points: 599 More actions · Answer 8

jwalin

Mr or Mrs. 500

Points: 599

May 11, 2009 at 2:36 pm

#992459

Perfect.. your are absolutely right

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

CREATE TABLE [dbo].[WCI_IP_ST_AGENT_SKILL_GROUP_HH](

[DateTime] [datetime] NULL,

[EmpNumber] [varchar](32) NOT NULL,

[Skill Group] [varchar](32) NOT NULL,

[SkillGroupSkillTargetID] [int] NOT NULL,

[SkillTargetID] [int] NOT NULL,

[wcEmployeeId] [int] NOT NULL,

[wcTaskId] [int] NOT NULL,

[PeripheralID] [int] NOT NULL,

[wcCallsAnswered] [int] NULL,

[wcTalkTime] [int] NULL,

...

[WhisperCallsToHalf] [int] NULL,

[EmergencyAssistsToHalf] [int] NULL,

[InterruptedTimeToHalf] [int] NULL,

[Ratio1] [decimal](9, 6) NULL CONSTRAINT [DF_WCI_IP_ST_AGENT_SKILL_GROUP_HH_Ratio1] DEFAULT (0),

[Ratio2] [decimal](9, 6) NULL CONSTRAINT [DF_WCI_IP_ST_AGENT_SKILL_GROUP_HH_Ratio2] DEFAULT (0)

) ON [PRIMARY]

Hmm, there no Primary Key on this table? And what about indexes?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

jwalin Mr or Mrs. 500 Points: 599 More actions · Answer 10

Yes there is no primary key and now index on this table .. Actualy this is my staging table where I update the records and then from here insert a records into actual table ( Staging ---> Production)