Jwalin Khatri

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)
AND DateTime>= CAST(Convert(varchar(10), getdate()-1, 101) as datetime)
AND DateTime 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)

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 ------ End Rounding correction
End --end @AgntAvailTimeOnDefaultSk 0 true
End --end @noAgentSG>1 true
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