Re: [RBASE-L] - Re: Do-Loop Processing Help

Mon, 24 Jul 2017 23:05:18 -0700

Buddy,

See attached file.

Larry

On Jul 24, 2017, at 11:15 PM, Buddy Walker <[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: [email protected] [mailto:[email protected]] On Behalf Of lwilson
Sent: Monday, July 24, 2017 10:49 PM
To: RBASE-L <[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 [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.

--
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.
   Table: ActualWorked         No Lock(s)
   Descr: AHL Data May - Jul 2016
   Last structure modification: 07/24/2017 19:48:21           

 No. Column Name        Attributes
 --- ------------------ ------------------------------------------------------
   1 Agent              Type   : TEXT 40
   2 Start_Date         Type   : DATE
   3 StartofShift       Type   : TIME
   4 EndOfShift         Type   : TIME
   5 StartOfFirstCall   Type   : TIME
   6 EndOfLastCall      Type   : TIME
   Current number of rows:      3149

 Agent                                    Start_Date StartofS EndOfShi StartOfF 
EndOfLas 
 ---------------------------------------- ---------- -------- -------- -------- 
-------- 
 Allen-Davis, Johari (11259)              07/22/2016 -0-      -0-      00:00:21 
23:38:03

   Table: AHL2                 No Lock(s)
   Descr: AHL Data May - Jul 2016
   Last structure modification: 07/23/2017 15:48:58           

 No. Column Name        Attributes
 --- ------------------ ------------------------------------------------------
   1 Customer_Start     Type   : DATETIME
   2 Customer_Start_Dat Type   : DATE
                                 Compute: Dextract(Customer_Start)
   3 Start_Year         Type   : TEXT 8
                                 Compute: (IYR4(Customer_Start))
   4 Start_Month        Type   : TEXT 8
                                 Compute: (TMON(Customer_Start_Date))
   5 Start_Day          Type   : TEXT 9
                                 Compute: (TDWK(Customer_Start))
   6 Start_Time         Type   : TIME
                                 Compute: (TEXTRACT(Customer_Start))
   7 Duration_End       Type   : DATETIME
   8 Customer_End_Time  Type   : TIME
                                 Compute: (TEXTRACT(Duration_End))
   9 Customer_Duration  Type   : INTEGER
                                 Compute: (Duration_End-Customer_Start)
  10 Customer_nbr       Type   : TEXT 12
  11 Contact_Direction  Type   : TEXT 8
  12 Delivery_Routing   Type   : TEXT 40
  13 Delivery_Queue     Type   : TEXT 20
  14 Disposition        Type   : TEXT 25
  15 Wait_End           Type   : DATETIME
  16 Wait_Duration      Type   : INTEGER
                                 Compute: (Wait_End-Customer_Start)
  17 Active_End         Type   : DATETIME
  18 Active_Duration    Type   : INTEGER
                                 Compute: (Active_End-Wait_End)
                                 Comment: Frim the end of the wait time through 
the end of the talk time = Total talk time
  19 On_Hold_End        Type   : DATETIME
  20 On_Hold_Duration   Type   : INTEGER
                                 Compute: (On_Hold_End-Wait_End)
  21 Contact_ID         Type   : INTEGER
  22 Agent              Type   : TEXT 40
  23 function           Type   : TEXT 40
  24 function_id        Type   : TEXT 8
  25 Shift              Type   : TEXT 15
   Current number of rows:     84171

 Agent                                    Start_Date StartofS EndOfShi StartOfF 
EndOfLas 
 ---------------------------------------- ---------- -------- -------- -------- 
-------- 
 Allen-Davis, Johari (11259)              07/22/2016 -0-      -0-      00:00:21 
23:38:03
 Agent                                    Customer_Start      Start_Ti Customer 
 ---------------------------------------- ------------------- -------- -------- 
 Allen-Davis, Johari (11259)              07/22/2016 16:57:04 16:57:04 16:59:12
 Allen-Davis, Johari (11259)              07/22/2016 17:10:21 17:10:21 17:10:21
 Allen-Davis, Johari (11259)              07/22/2016 17:10:33 17:10:33 17:10:45
 Allen-Davis, Johari (11259)              07/22/2016 17:11:15 17:11:15 17:11:52
 Allen-Davis, Johari (11259)              07/22/2016 17:25:44 17:25:44 17:25:45
 Allen-Davis, Johari (11259)              07/22/2016 17:25:58 17:25:58 17:29:19
 Allen-Davis, Johari (11259)              07/22/2016 17:34:53 17:34:53 17:46:48
 Allen-Davis, Johari (11259)              07/22/2016 17:42:48 17:42:48 17:47:37
 Allen-Davis, Johari (11259)              07/22/2016 18:18:09 18:18:09 18:21:41
 Allen-Davis, Johari (11259)              07/22/2016 18:34:08 18:34:08 18:37:34
 Allen-Davis, Johari (11259)              07/22/2016 19:51:06 19:51:06 20:02:36
 Allen-Davis, Johari (11259)              07/22/2016 20:24:17 20:24:17 20:26:27
 Allen-Davis, Johari (11259)              07/22/2016 20:33:47 20:33:47 20:37:44
 Allen-Davis, Johari (11259)              07/22/2016 21:22:27 21:22:27 21:24:22
 Allen-Davis, Johari (11259)              07/22/2016 21:55:56 21:55:56 21:58:58
 Allen-Davis, Johari (11259)              07/22/2016 22:24:52 22:24:52 22:25:22


--
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.

Reply via email to