Sorry for the delay. I'm not sure what you are now asking. For a 9AM - 9PM run your table would contain data like so:
FoodBankID | Year | Month | Day | HourSlot 2 | 2006 | 3 | 10 | 9 2 | 2006 | 3 | 10 | 10 2 | 2006 | 3 | 10 | 11 2 | 2006 | 3 | 10 | 12 2 | 2006 | 3 | 10 | 13 2 | 2006 | 3 | 10 | 14 2 | 2006 | 3 | 10 | 15 2 | 2006 | 3 | 10 | 16 2 | 2006 | 3 | 10 | 17 2 | 2006 | 3 | 10 | 18 2 | 2006 | 3 | 10 | 19 2 | 2006 | 3 | 10 | 20 To add 7AM - 8AM for the same food bank, or whatever grouping you have, just add more records. Hope that helps -Daniel -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sophek Tounn Sent: Thursday, March 09, 2006 11:28 AM To: Dallas/Fort Worth ColdFusion User Group Mailing List Subject: Re: [DFW CFUG] Looping question, need help urgently Hi Daniel And All, Thank you all for commenting. Here is what is happening in the App. The admin will create a slot form 9AM - 9PM for example, if he chooses to enter only 1 date in the date range field, he'll created 1 slot for 1 day, but if he enters a date range he can create 1 slot for multiple days. So if he enters 3/01/06 - 03/04/06 , Reservation Time of 9AM thru 9 PM, he'll have a slot for 9AM - 9 PM for 3-1,3-2,3-3,3-4, I could use the hour slot like you use suggest but I want the flexibility for the admin to create a range of hours without having to enter individual hours. So the problem is when the admin enters a slot that over lapse the 9AM-9PM slot, the system should give an error, lets say if he enters: 10AM - 11AM = error 2 PM - 3 PM = error 4 PM - 6 PM = error All these should error because they are within the slot that is already allocated. So , If he enters 7-8 AM it should work, and it does. So now he has 2 slots : 7AM - 8AM 9AM - 9PM So with 1 slot I can do something like this: SELECT ScheduleDate, StartTime, EndTime from schedule_master where StartTime between 900 AND 1700 But how to I check again the other slot the 7-8 AM in my query? Thanks All, On 3/7/06, Daniel Elmore <[EMAIL PROTECTED]> wrote: 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/ _______________________________________________ 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/
