Problem with nested WHILE statement, looping through recordset

[PRMiller]

On a post at

http://www.tek-tips.com/viewthread.cfm?qid=926902,

DSect pasted some code to loop through a recordset. I understand that looping tends to be pretty hard on the server, but so far this is the only solution I've been able to identify. Unfortunately (and, of course), I have a problem. First, I'll explain my objective.

I am creating a report that will list all times employees are scheduled in a call center on a given day. Schedule information is stored in two tables, as follows:

tbl_IEX_A_schedheader

iSCHED_ID iAGENT_ID datDATE    sSTART_MINUTE sSCHED_LENGTH sEXCEP_CODE  
2264347   1810      2005-11-18 705           495           46

and tbl_IEX_A_scheddetail

nDetail_pk   iSCHED_ID   sEXCEP_CODE   sSTART_MINUTE   sLENGTH
2165912      2264347     76            945             30
2165914      2264347     66            825             15
2165932      2264347     66            1095            15

Now, it would be an excercise in simplicity to run a query that joins the two together, yielding a total of 4 records for this employee, on this date. Alas, that is not the case.

The sEXCEP_CODE in the header table is considered the employee's default work state. That is, if they are not engaged in any activity defined by the sEXCEP_CODE in the detail table, they are assumed to be engaged in the activity designated as the default. So, in this employee's case, they are doing activity 46 all day, with the exception of the 60 minutes they are engaged in activities 76 or 66.

So, my report needs to show that. For every employee. We're talking a significant number of records here, as employees are scattered across various call centers.

To achieve the objective, I decided to create a temporary table, populating it with the one record from the header table and three records from the detail table, then filling in all "free time" with the default EXCEP_CODE listed in the header table.

To do this, I used DSect's code. I've pasted it below.
Please note that the fields defined in the query are correct -- the ones listed above were abbreviated somewhat for readability. So sEXCEP_CODE is actually sintEXCEP_CODE, and so on.

--/*******************************************************************************
-- *  Created on:  11/11/2005
-- *  Last Updated:  11/11/2005
-- *  Created By:  xxxxxx
-- *
-- *  Purpose:  xxxxx Over/Under
-- *  Dependencies:  
-- *  History of Changes:
-- *                       
-- *
-- **/


--CREATE PROCEDURE [dbo].[usp_Over_Under_a] 

--  @datStartDate As datetime

--AS

Declare @datStartDate datetime
Set @datStartDate = '2005-11-18'

--Step 1 - Create temporary table populated with schedheader records
If Object_ID('tempdb..#TEMP1') Is Not Null
  Begin
    DROP TABLE #TEMP1
  End

CREATE TABLE #TEMP1
  (numKey_pk Numeric Identity (1,1) NOT NULL, intSCHED_ID Int, intAGENT_ID Int, 
  datDATE SmallDateTime, sintSTART_MINUTE SmallInt, intStartSecond Int, 
  sintEXCEPTION_CODE SmallInt, sintSCHEDULE_LENGTH SmallInt)

INSERT INTO #TEMP1
SELECT 
  intSCHED_ID,
  intAGENT_ID,
  datDATE, 
  sintSTART_MINUTE, 
  (sintSTART_MINUTE * 60),
  sintEXCEPTION_CODE, 
  sintSCHEDULE_LENGTH
FROM tbl_IEX_A_schedheader
WHERE 
  datDATE = @datStartDate
  AND intAgent_ID = 1810




--Step 2 - Populate temp table with scheddetail records
SET NoCount On

Declare @iReturnCode int
Declare @iNextAgentID int
Declare @iCurrentAgentID int
Declare @iLoopControl int

Declare @iSchedID int


--Initialize variables
SELECT @iLoopControl = 1
SELECT @iNextAgentID = Min(intAGENT_ID)
FROM #TEMP1
SELECT @iCurrentAgentID = @iNextAgentID
FROM #TEMP1

SELECT @iSchedID = intSCHED_ID
FROM #TEMP1
WHERE 
  intAGENT_ID = @iNextAgentID




--Start the main processing loop
While @iLoopControl = 1
  Begin
    INSERT INTO #TEMP1
      (intSCHED_ID, intAGENT_ID, datDATE, sintSTART_MINUTE, 
      intStartSecond, sintEXCEPTION_CODE, sintSCHEDULE_LENGTH)
    SELECT  
      intSCHED_ID, @iNextAgentID, @datStartDate, sintSTART_MINUTE,
      (sintSTART_MINUTE * 60), sintEXCEPTION_CODE, sintLENGTH
    FROM tbl_IEX_A_scheddetail
    WHERE
      intSCHED_ID = @iSchedID



    --Reset looping variable
    SELECT @iNextAgentID = NULL
    
    SELECT @iNextAgentID = Min(intAGENT_ID)
    FROM #TEMP1
    WHERE 
      intAGENT_ID > @iCurrentAgentID

    If IsNull(@iNextAgentID,0) = 0
      Begin
        Break
      End


  End

select * from #TEMP1
--GO

Now, this code works for populating every existing record from the header and detail tables.

To start creating the temporary records, I began by nesting a WHILE statement. I inserted this code:

    --Populate temp table with default exception code during gaps
    Declare @sStartMinute smallint
    Declare @iStartSecond int
    Declare @iExceptionCode int
    Declare @sScheduleLength smallint

    Declare @iCurrentMinute int
    Declare @iNextMinute int
    Declare @iMinuteLoopControl int

    SELECT @iMinuteLoopControl = 1
    SELECT @iNextMinute = Min(sintSTART_MINUTE)
    FROM #TEMP1
    WHERE intSCHED_ID = @iSchedID

    SELECT @iCurrentMinute = @iNextMinute
   
    --Start the minute sub-processing loop
    While @iMinuteLoopControl = 1
      Begin

	-- **** PROCESSING STEPS HERE **** 

        --Reset minute looping variable
        SELECT @iNextMinute = 0

        SELECT @iNextMinute = Min(sintSTART_MINUTE)
        FROM #TEMP1
        WHERE 
          intSCHED_ID = @iSchedID
          AND sintSTART_MINUTE > @iCurrentMinute

        If IsNull(@iNextMinute,0) = 0
          Begin
	    Break
          End

      End

Directly above the "--Reset looping variable" comment 13 lines up from the bottom of the code. Obviously there are no processing steps as of yet. I planned on finding the second lowest -- or earliest -- minute in the day, then the third lowest minute, finding the difference, and inserting a record.

For example, the code should find that 825 is the second lowest minute of the day, at exception code 66. We already know from the header table that the employee's day started at minute 705, and he was engaged in activity 46 until minute 825.

We now need to fill in the gap between minute 840 (minute 825 plus the length of 15) and minute 945. Since the difference is 105 minutes, I would insert a record the says

nDetail_pk   iSCHED_ID   sEXCEP_CODE   sSTART_MINUTE   sLENGTH
             2264347     46            840             105

and so on.

I'm not sure if I explained this well enough. The problem came when I tested the code without adding any new processing steps (wanted to test the loop first) -- I was stuck in an endless loop. My questions:

a) Can I display debug results in the query analyzer while a query is runnign, so I know where I got stuck?

b) Is there a better way to do this??

Thanks,
Paul

 

[sodakotahusker]

you can insert select or print statements at stategic locations so you can see into the process. Running this through the debugger instead of query Analyzer would be a better way to diagnose the problem.

 

[PRMiller]

Found the problem - forgot the return statement at the end of the loops!

I'd still be interested in hearing of alternatives ways to do this, but I think I'm stuck with it for a while!