Larry
Here is the adjusted code.
You may want to re-think your table structures.
I noticed you have a column name “function” which is a reserved word. This
may cause you problems later on. Also I’m not sure why you have a lot of
computed columns because you can always get the information on the fly. But
again it be the way things have to work.
**********************Start Here*****************
SET VAR vagenttxt TEXT
SET VAR vdateworked DATE
SET VAR vminstart TIME
SET VAR vmaxend TIME
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
{
You may want add a where for specific day or range. Keep in mind since you are
storing start and end with the agent (which is only one row) you
overwriting the startOfFirstCall and EndOfLastCall
}
DECLARE c1 CURSOR FOR SELECT agentcolumn,dateworkedcolumn +
FROM actualworkedtbl
OPEN c1
FETCH c1 INTO +
vagenttxt INDICATOR ivvar1, +
vdateworked INDICATOR ivvar2
WHILE SQLCODE <> 100 THEN
SELECT MIN(start_time),MAX(customer_end_time) INTO vminstart INDICATOR iv1,
vmaxend INDICATOR iv2 +
FROM ahl2 WHERE DEXTRACT(customer_start) = .vdateworked
UPDATE actualworkedtbl SET startoffirstcall = .vminstart, endoflastcall =
.vmaxend WHERE CURRENT OF c1
FETCH c1 INTO +
vagenttxt INDICATOR ivvar1, +
vdateworked INDICATOR ivvar2
ENDWHILE
DROP CURSOR c1
LABEL done
CLEAR ALL VARIABLES
RETURN
**********************End Here******************
From: [email protected] [mailto:[email protected]] On Behalf Of
Larry Wilson
Sent: Tuesday, July 25, 2017 2:05 AM
To: [email protected]
Subject: Re: [RBASE-L] - Re: Do-Loop Processing Help
Buddy,
See attached file.
Larry
On Jul 24, 2017, at 11:15 PM, Buddy Walker <[email protected]
<mailto:[email protected]> > wrote:
Without knowing your table and column layout it is hard to guess what the
problem would be; therefore, my code is all assumption.
You need to make sure you are looking at the data based on date. If there is
more than one date for an agent it will be updated with the last date found.
Buddy
From: <mailto:[email protected]> [email protected] [
<mailto:[email protected]> mailto:[email protected]] On Behalf Of
lwilson
Sent: Monday, July 24, 2017 10:49 PM
To: RBASE-L < <mailto:[email protected]> [email protected]>
Subject: [RBASE-L] - Re: Do-Loop Processing Help
Thanks Again Buddy,
The process completed. However, the updated times were not correct. It appears
that all of them were way off.
Example the results for very first Agent:
Production Table:
Start for 1st Call: 16:59:04
End of Last Call: 22:25:22
ActualWorked (Updated:
StartOfFirstCall: 00:00:21
EndOfLastCall: 23:38:03
These times are way off. Most of the updated StartOfFirstCall is around
:00:00:01 and 01:00:00
Below is the updated script:
**************************************************
SET VAR vagenttxt TEXT
SET VAR vdateworked DATE
SET VAR vminstart TIME
SET VAR vmaxend TIME
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT Agent, Start_date +
FROM ActualWorked
OPEN c1
FETCH c1 INTO +
vagenttxt INDICATOR ivvar1, +
vdateworked INDICATOR ivvar2
WHILE SQLCODE <> 100 THEN
SELECT MIN(start_time), MAX(Customer_End_Time) INTO vminstart INDICATOR
iv1, vmaxend INDICATOR iv2 +
FROM AHL2 WHERE DEXTRACT(Customer_Start) = .vdateworked
UPDATE ActualWorked SET StartOfFirstCall = .vminstart, EndOfLastCall =
.vmaxend WHERE CURRENT OF c1
FETCH c1 INTO +
vagenttxt INDICATOR ivvar1, +
vdateworked INDICATOR ivvar2
ENDWHILE
DROP CURSOR c1
LABEL done
CLEAR ALL VARIABLES
RETURN
**********************************************************
On Monday, July 24, 2017 at 12:27:23 PM UTC-4, lwilson wrote:
Do-Loop Help in Updating a Table
I know this can be done with a ‘Do-Loop.” However, as a returning RBase users,
candidly I have forgotten how.
I think it is combination of Cursor, While & Endwhile. Just not sure how to set
the script.
Can anyone provide a sample script for how to set this up.
Scenario:
Small Call Center with 15 Agents
Volume approx. 84,000 calls/year
Problem:
Validate that the Agents have worked their Scheduled shift/hours
Database Tables
Production table with all call activity 'AHL' (Multiple Calls per
day)
Date/Time of Call (DATETIME)
Start of Call Time (TIME)
End of Call Time (TIME)
Agent (TEXT)
'Actual_Work' (One record for each actual day worked for each Agent)
Agent (TEXT)
Date Worked (DATE)
Shift Ex. 7:30AM-3:30PM (TEXT)
Scheduled Start Time (TIME)
Scheduled End Time (TIME)
NEW COLUMNS TO BE UPDATED
Start_of_First_Call (first call of the day)
(TIME)
End_of_Last_Call (Last call for the day)
(TIME)
Once I get the table updated, I want to compare the ‘Scheduled’ Shift hours to
the Actual ‘Hours Worked.
The benchmark measures whether agents are on time for their shifts, whether
they go to lunch and take breaks according to the schedule. This is
particularly important in smaller call centers because there is not as much
room for error. Some small centers may only have 2 agents on call at any time.
The global metric is 95%.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to <mailto:[email protected]>
[email protected].
For more options, visit <https://groups.google.com/d/optout>
https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to <mailto:[email protected]>
[email protected].
For more options, visit <https://groups.google.com/d/optout>
https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected]
<mailto:[email protected]> .
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
