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
</CFQUERY>
<!--- sets todays date --->
<cfset todaysdate = #CreateODBCDate(NOW())#>
<cfoutput>
<!---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">)
</CFQUERY>
</cfloop>
</cfoutput>
<!--- Set the variable CountVar to 0. --->
<!--- Get the about record --->
<CFQUERY DATASOURCE="databaseExample" name="CounselorTB">
SELECT *
FROM CounselorTable
</CFQUERY>
<cfset temp = ValueList(CounselorTB.ID)>
<cfloop index = "LoopCount" from = "1" to = "#CounselorTB.recordcount#">
<CFSET CounselorTBArray[loopcount]=trim(listGetAt(temp, loopcount))>
</cfloop>
<!--- 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>
<cfelse>
<cfset CountVar = 1>
<cfset Done = Done + 1>
<cfif done is 10>
<cfabort>
</cfif>
</cfif>
<!--- 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
</CFQUERY>
<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>
</cfif>
<cfelse>
<CFQUERY DATASOURCE="databaseExample">
UPDATE ScheduleTable
SET Counselor = <cfqueryparam value="#CounselorTBArray[CountVar]#" cfsqltype="cf_sql_varchar">
WHERE SchedDateAct = #CreateODBCDate(todaysdate)# AND StudentID = 7
</CFQUERY>
</cfif>
</cfif>
</cfloop>
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.
7
Replies
AdChoices