Hey Sophek, what you're doing shouldn't be hard if your data is normalized further.
If you had a table storing: FoodBankID | Year | Month | Day | HourSlot You could check if the time slot for 9 - 12 is free by using a query like so: SELECT FoodBankID,HourSlot FROM that_table WHERE year = year(form.date) AND month = month(form.date) AND day = day(form.date) AND HourSlot BETWEEN #form.startTime# AND #form.endTime# If no records are found, the time slot is open, otherwise you get the data for which bank is scheduled at one/all of those hours. Use military time on the DB side to avoid messing with some AM/PM field. Let the database do this kind of work, not CF, it will make your life easier! Hope that helps, Daniel -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sophek Tounn Sent: Tuesday, March 07, 2006 5:24 PM To: Dallas/Fort Worth ColdFusion User Group Mailing List Subject: [DFW CFUG] Looping question, need help urgently Hi All, I'm not too good with loop here a question I have and hopefully someone can help me? I'm building an online scheduler for a Food bank client of ours where he can schedule for delivery, pickup, shopping etc. I have an interface where they can pick the schedule type, chose the date, and begin time and end time, also the time interval and agencies per slot. Lets say I enter a slot time of 9 AM - 12 AM (midnight), so that 9-12 AM slot is taken, lets say I also have a 7AM -8AM slot taken as well, If I have just one slot (9-12) only in my DB then I'm fine, but when there are more than 1 slot is where it gets complicated. Here is the URL to test it out: http://www.aidmatrixeurope.org/scheduler/enterslot.cfm username and password: admin admin 0040 Below is my code: <cfset FinalStartTime = Form.StartTime & ":" & Form.StartTime_Min & ":00" & "#Form.ST_TT#"> <cfset FinalEndTime = Form.EndTime & ":" & Form.EndTime_Min & ":00" & "#Form.ET_TT#"> <!--- If the scheduleDateEnd is not filled in, just do a plain insert ---> <cfif form.ScheduleDateEnd EQ ""> <cfquery name="insertslotcount" datasource="mydatasource"> Select FoodBankCode , ScheduleDate , StartTime , EndTime , SlotInterval , AgenciesPerSlot , ScheduleType , CreatedBy , CreatedDate from schedule_master where FoodBankCode = <cfqueryparam value="#SESSION.Auth.FoodbankCode#" cfsqltype="CF_SQL_VARCHAR"> AND ScheduleDate = <cfqueryparam value="#DateFormat(form.ScheduleDate,"yyyy-mm-dd")#" cfsqltype="CF_SQL_VARCHAR"> <!--- AND StartTime = <cfqueryparam value="#TimeFormat(FinalStartTime,"HH:MM:SS")#" cfsqltype="CF_SQL_VARCHAR"> OR EndTime = <cfqueryparam value="#TimeFormat(FinalEndTime,"HH:MM:SS")#" cfsqltype="CF_SQL_VARCHAR"> ---> AND ScheduleType = <cfqueryparam value="#form.ScheduleType#" cfsqltype="CF_SQL_VARCHAR">; </cfquery> <cfdump var="#insertslotcount#"> <!--- Get the Hour of the DBStarTime ---> <!--- If hour is Midnight, convert to 24 because the logic will not work if I don't do that ---> <cfif Hour(insertslotcount.StartTime) EQ 0> <cfset dbStartTime = 24> <cfelse> <cfset dbStartTime = #Hour(insertslotcount.StartTime)#> <!--- 11 PM---> </cfif> <!--- Get the Hour of the EndTime ---> <!--- If hour is Midnight, convert to 24 because the logic will not work if I don't do that ---> <cfif Hour(insertslotcount.EndTime) EQ 0> <cfset dbEndTime = 24> <cfelse> <cfset dbEndTime = #Hour(insertslotcount.EndTime)#> <!--- 11 PM---> </cfif> <!--- Get the Hour of the User FinalStartTime ---> <!--- If hour is Midnight, convert to 24 because the logic will not work if I don't do that ---> <cfif Hour(FinalStartTime) EQ 0> <cfset UserStartTime = 24> <cfelse> <cfset UserStartTime = #Hour(FinalStartTime)#> </cfif> <!--- Get the Hour of the User FinalEndTime ---> <!--- If hour is Midnight, convert to 24 because the logic will not work if I don't do that ---> <cfif Hour(FinalEndTime) EQ 0> <cfset UserEndTime = 24> <cfelse> <cfset UserEndTime = #Hour(FinalEndTime)#> </cfif> <cfif (UserStartTime LTE dbStartTime) AND (UserEndTime LTE dbStartTime) OR (UserStartTime GTE dbStartTime) AND (UserEndTime GTE dbEndTime)> <cfset returnRespone = "Insert into the database"> <!--- Insert into the database ---> <cfelse> <!-- Error slot is already taken --> <cfset returnRespone = "Your Entry of" & FinalStartTime & " " & FinalEndTime> </cfif> </cfif> thanks in advance _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
