7 Replies Latest reply on Jun 13, 2011 9:46 AM by -==cfSearching==-

    Lists, Arrays, Recordsets


      Not sure how to label this issue that I am trying to solve...


      What  I am trying to do is take a list of students from a database query  recordset and assign them the a counselor from a database query  recordset and place them in an alternating rotation Monday through  Friday for the remainder of the year.


      Example of desired result.



      student1 - June 6,   2011Counselor1
      student1 - June 7, 2011Counselor2
      student1 - June 8, 2011Counselor3
      student1 - June 9, 2011Counselor4
      student1 - June 10, 2011Counselor5
      student1 - June 11, 2011Counselor6
      student1 - June 12, 2011Counselor7
      student1 - June 13, 2011Counselor8
      student1 - June 14, 2011Counselor9
      student1 - June 15, 2011Counselor10
      student1 - June 16, 2011Counselor11
      student1 - June 17, 2011Counselor12
      student1 - June 18, 2011Counselor1
      student1 - June 19, 2011Counselor2
      student1 - June 20, 2011Counselor3
      student1 - June 21, 2011Counselor4
      student1 - June 22, 2011Counselor5
      student1 - June 23, 2011Counselor6
      student1 - June 24, 2011Counselor7
      student1 - June 25, 2011Counselor8
      student1 - June 26, 2011Counselor9
      student1 - June 27, 2011Counselor10
      student1 - June 28, 2011Counselor11
      student1 - June 29, 2011Counselor12
      student1 - June 30, 2011Counselor1
      student2 - June 6, 2011Counselor2
      student2 - June 7, 2011Counselor3
      student2 - June 8, 2011Counselor4
      student2 - June 9, 2011Counselor5
      student2 - June 10, 2011Counselor6
      student2 - June 11, 2011Counselor7
      student2 - June 12, 2011Counselor8
      student2 - June 13, 2011Counselor9
      student2 - June 14, 2011Counselor10
      student2 - June 15, 2011Counselor11
      student2 - June 16, 2011Counselor12
      student2 - June 17, 2011Counselor1
      student2 - June 18, 2011Counselor2
      student2 - June 19, 2011Counselor3
      student2 - June 20, 2011Counselor4
      student2 - June 21, 2011Counselor5
      student2 - June 22, 2011Counselor6
      student2 - June 23, 2011Counselor7
      student2 - June 24, 2011Counselor8
      student2 - June 25, 2011Counselor9
      student2 - June 26, 2011Counselor10
      student2 - June 27, 2011Counselor11
      student2 - June 28, 2011Counselor12
      student2 - June 29, 2011Counselor1
      student2 - June 30, 2011Counselor2
      student3 - June 6, 2011Counselor3
      student3 - June 7, 2011Counselor4
      student3 - June 8, 2011Counselor5
      student3 - June 9, 2011Counselor6
      student3 - June 10, 2011Counselor7
      student3 - June 11, 2011Counselor8
      student3 - June 12, 2011Counselor9
      student3 - June 13, 2011Counselor10
      student3 - June 14, 2011Counselor11
      student3 - June 15, 2011Counselor12
      student3 - June 16, 2011Counselor1
      student3 - June 17, 2011Counselor2
      student3 - June 18, 2011Counselor3
      student3 - June 19, 2011Counselor4
      student3 - June 20, 2011Counselor5
      student3 - June 21, 2011Counselor6
      student3 - June 22, 2011Counselor7
      student3 - June 23, 2011Counselor8
      student3 - June 24, 2011Counselor9
      student3 - June 25, 2011Counselor10
      student3 - June 26, 2011Counselor11
      student3 - June 27, 2011Counselor12
      student3 - June 28, 2011Counselor1
      student3 - June 29, 2011Counselor2
      student3 - June 30, 2011Counselor3


      I  have tried a combination of lists, nested index loops, and mutli  dimensional arrays. I have seemed to be dead locked. This is a start to  the example of what I have been trying and I am sure you can see it  isn't the desired result. It will provide only a student schedule  without an assigned counselor. The problem has been fitting the  counselor into the rotation.



      <cfset daysleft = DaysinYear(todaysDate) - DayofYear(todaysDate) >


      <cfoutput query="StudentQuery">
          <cfloop index = "LoopCount" from = "0" to = "#daysleft#">
              <cfset scheduledate = DayOfWeek(todaysdate+Loopcount)>
                  <cfif DayOfWeek(scheduledate) is not 7 AND DayOfWeek(scheduledate) is not 1>
                       #StudentName# - #DayofWeekAsString(DayOfWeek(scheduledate))# -  #DateFormat((todaysdate+Loopcount), 'long')#<br>
          </cfloop><br />


      Keep  in mind there needs to be an finite number of students and counselors  determined by database records that do change. The plan is to rerun the  script weekly as these records change. The dates are finite and simply  reduce from week to week. December 31st is the last day. Any help is  appreciated.

        • 1. Re: Lists, Arrays, Recordsets
          -==cfSearching==- Level 4
          The problem has been fitting the  counselor into the rotation.

          I would bet money there is a slicker way to do this. But you are not too far off from a raw first pass.


          Inside your date loop, grab a row from the counselors query.  Save the row number and increment it each time you loop. So you grab a different counselor for each date. When you reach the last record in the query, reset the row number. So you continuously loop until all dates are processed.  To alternate, just

          increment the initial counselor row after each student.


          There are still issues you would need to handle, such as  overscheduling if there were more students than counselors. But maybe this will spark a few ideas ...


          <cfoutput query="StudentQuery">
              <!--- increment starting row --->
              <cfset counselorRow = currentRow>
              <cfloop index="LoopCount" from="0" to="#daysleft#">
                   #CounselorQuery.Name[ counselorRow ]# .... 
                  <!--- move to next row --->    
                  <cfset counselorRow++>
                  <!--- continuously loop through query --->    
                  <cfif counselorRow gt qTeachers.recordCount>
                      <cfset counselorRow = 1>
          1 person found this helpful
          • 2. Re: Lists, Arrays, Recordsets
            CFChrisPilie Level 1

            cfsearching... thanks for the reply! This one seemed rather involved an wasn't sure that I would get any help. I actually had been hunting in the right location, I just wasn't sure how to do it. Your answer helped me definitly feel more confident in my results. The problem actually got a little more complicated when the client told me that there needed to be limits on the counelor's schedule.. grrr. Anyhow... here is what I came up with so far. It is a frankenstein but it works pretty good.


                    <!--- Get the TBCounselor records --->
                <CFQUERY DATASOURCE="databaseExample" name="Schedule">
                SELECT s.Scheduledate, s.Couselor, r.LimitationNumber, COUNT(Couselor) AS CounselorCount, r.ID
                FROM ScheduleTable s INNER JOIN CounselorTable r
                    ON s.Couselor = r.ID
                WHERE Limitation > 0
                GROUP BY s.Scheduledate, s.Couselor, r.LimitationNumber, r.ID
                ORDER BY s.Couselor


            <!--- sets todays date --->
            <cfset todaysdate = #CreateODBCDate(NOW())#>


                    <!---Find the dates for the next week --->
                    <cfloop index = "LoopCount" from = "0" to = "60">
                        <cfset Scheddate = DayOfWeek(todaysdate+Loopcount)>
                        <cfset SchedDateAct = todaysdate+Loopcount>
                        <cfset ScheduleID = "#CreateUUID()#"> 
                            <CFQUERY DATASOURCE="databaseExample">
                                INSERT INTO ScheduleTable(ID,SchedDateAct,StudentID,StudentIndex,Counselor)
                                VALUES(<cfqueryparam value="#ScheduleID#" cfsqltype="cf_sql_varchar">,
                                    <cfqueryparam value="#SchedDateAct#" cfsqltype="cf_sql_date">,
                                    <cfqueryparam value="7" cfsqltype="cf_sql_varchar">,
                                    <cfqueryparam value="#Loopcount#" cfsqltype="cf_sql_integer">,
                                    <cfqueryparam value="0" cfsqltype="cf_sql_varchar">)
            <!--- Set the variable CountVar to 0. --->
                <!--- Get the about record --->
                <CFQUERY DATASOURCE="databaseExample" name="CounselorTB">
                SELECT *
                FROM CounselorTable
                    <cfset temp = ValueList(CounselorTB.ID)>
                    <cfloop index = "LoopCount" from = "1" to = "#CounselorTB.recordcount#">
                        <CFSET CounselorTBArray[loopcount]=trim(listGetAt(temp, loopcount))>
            <!--- sets CounselorTBaurant count index --->   
            <cfset CountVar = 0>
            <!--- sets abort mechanism --->
            <cfset done = 1>
            <cfset pass = "no">
            <!--- set for loop allowance to return to CounselorTB.recordcount --->
            <cfset RecordCount = CounselorTB.Recordcount -1>
            <!--- sets loop condition that allows loop to continue after recordcount has been achieved --->
            <cfset RecordCount2 = CounselorTB.Recordcount +1>


            <!--- Loop until CountVar = 5. --->
            <cfloop condition = "CountVar LESS THAN OR EQUAL TO Recordcount2">
            <!--- increment todays date for date alignment --->
                <cfset todaysdate = todaysdate + 1>


            <!--- Check for date exclusions --->
                <cfif DayOfWeek(todaysdate) is not 7 AND DayOfWeek(todaysdate) is not 1>
            <!--- create loop conditions form 10 cycles --->       
                    <cfif CountVar LTE Recordcount>
                        <cfset CountVar = CountVar+1>
                        <cfset CountVar = 1>
                        <cfset Done = Done + 1>
                            <cfif done is 10>
            <!--- update schedule with CounselorTBaurants --->
                            <!--- Get the TBStudent Counselor records that contain limits --->
                <CFQUERY DATASOURCE="databaseExample" name="Schedule">
                SELECT s.SchedDateAct, s.Counselor, r.LimitationNumber, COUNT(CounselorTB) AS CounselorCount
                FROM ScheduleTable s INNER JOIN CounselorTable r
                    ON s.Counselor = r.ID
                WHERE Limitation > 0 AND s.Counselor = '#CounselorTBArray[CountVar]#' AND s.SchedDateAct = #CreateODBCDate(todaysdate)#
                GROUP BY s.SchedDateAct, s.Counselor, r.LimitationNumber
                    <cfif schedule.recordcount is not 0>
                        <cfif Schedule.LimitationNumber GT Schedule.CounselorCount>
                            <CFQUERY DATASOURCE="databaseExample">
                                UPDATE ScheduleTable
                                SET Counselor = <cfqueryparam value="#CounselorTBArray[CountVar]#" cfsqltype="cf_sql_varchar">
                                WHERE SchedDateAct = #CreateODBCDate(todaysdate)# AND StudentID = 7
                            <CFQUERY DATASOURCE="databaseExample">
                                UPDATE ScheduleTable
                                SET Counselor = <cfqueryparam value="#CounselorTBArray[CountVar]#" cfsqltype="cf_sql_varchar">
                                WHERE SchedDateAct = #CreateODBCDate(todaysdate)# AND StudentID = 7


            It isn't pretty but it works so far. I really don't like the queries in the loops but have tried a number of different configurations and this has worked best. I am also curious about the syntax in your example <cfset counselorRow++>. I haven't seen that one before.

            • 3. Re: Lists, Arrays, Recordsets
              CFChrisPilie Level 1

              Lets call this one answered... Thanks cfsearching. Feel free to offer any further advice.

              • 4. Re: Lists, Arrays, Recordsets
                -==cfSearching==- Level 4

                I cannot say I understand what is the code is doing, but it does look like a lot of complexity was added. What exactly are these "limits on the counselor's schedule" (in plain english)?


                curious about the syntax in your example

                cfset counselorRow++


                It is just a shorthand way of incrementing. Essentially the same as:

                http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec09d55 -7ffc.html

                • 5. Re: Lists, Arrays, Recordsets
                  CFChrisPilie Level 1

                  Some counselors have limited schedules throughout the day. It would be impracticle to think that one counselor could meet with 20 students in one day so we have created limits. What the script does is loop through the dates and insert them into the database. It then comes back and checks to see if there are limits for the cousnelor. If there are limits, it checks how many visits they can have and stops assigning appointments at thier limited number.

                  • 6. Re: Lists, Arrays, Recordsets
                    CFChrisPilie Level 1

                    Oh.. this is only for 1 student. Each student will initiate their scehdule. If I can get this script widdled down to something less complex, I may do it as a scheduled task. The scheduled task would loop through all the students. Ideally, the student and couselor will have a 2 week window to see their scedule. As students and couselors are added, the scheduled task will "reshuffle the deck" as new students and new counselors are added. The goal now is to eliminate the multiple inserts and remove the queries inside the loops.

                    • 7. Re: Lists, Arrays, Recordsets
                      -==cfSearching==- Level 4

                      The goal now is to eliminate the

                      multiple inserts


                      You cannot avoid it with your current method. Since you are generating the dates one at a time, you have to insert them that way. To insert multiple records at once you would need to move more of the logic to the database side (which might not be a bad idea..) Using a calendar table would allow you to insert a range of dates in one statement using INSERT INTO/SELECT. Depending on which database you are using there may be other options as well. For example MS SQL 2005/2008 can do some pretty slick stuff with CTE.