November 19, 2008 at 9:37 am
Not really. Based on that some of the sample data should have been rejected. Could you please explain the results expected based on the sample data. I'm sorry, but I'm still somewhat confused. I can understand a hotel reservation system, but I'm not seeing the relationship between that and your problem from looking at the sample data provided.
November 19, 2008 at 9:59 am
OK I'll try.
ok assumption that the user wants the:
CheckInDate = 11/11/2008
CheckOutDate = 11/14/2008
RunType = Large
RunNo = L100
Insert Allowed
---------------
1 N - the dates, runtype and runno are the booked already
2 Y - checking in is allowed on the same date another is checking out
3 Y - the dates, runtype and runno are not booked for those dates
4 Y - same as #2 only reversed
5 N - cant book since 11/11 and 11/12 are already booked (overlapping)
6 N - cant book since 11/12 - 11/14 are already booked (overlapping)
Hope this helps...
November 19, 2008 at 10:51 am
It does, but it points out an error in your sample data as well. If we make the assumption (as ReservationID is an identity column) that the reservations were entered in ReservationID order, reservation id numbers 5 and 6 should have been rejected due to overlaps with the first 4 entries.
Do you concur with this assessment?
November 19, 2008 at 12:08 pm
Yes. I think I see your confusion now. We can assume:
CheckInDate = 11/11/2008
CheckOutDate = 11/14/2008
RunType = Large
RunNo = L100
is already in the db.
What I am looking for is a SELECT query (see first post - attempt) that will return something (possibly a Y/N) for any attemps for the INSERTS that I have a 'N'' next to.
If any of the INSERTS that I have a 'Y' next to would return something saying there is no 'conflict' or 'overlap' and thus a reservation can be made with those dates/runtype/runno.
Does that make it a little clearer?
November 19, 2008 at 12:16 pm
Nope, doesn't help. Based on your current sample data, we have 6 reservations for a Large runtype, and run number L100. Based on the information provided, 2 of those are in error; same run type and run number with dates that overlap existing reservations, and you want to make a 7th reservation that will conflict with one or more of the reservations already in the system.
Missing anything or am I being too critical? Have to remember, computer systems do exactly what they are told to do, not what you want them to do (gee, sounds like children).
November 19, 2008 at 12:21 pm
Here is a question that should be asked of the system:
Given:
CheckInDate = 11/11/2008
CheckOutDate = 11/14/2008
RunType = Large
What RunNo is available?
Based on the sample data and table information, we can't really ask that question. We don't have a table describing the RunNo's (RunNo, RunType, et al.).
November 19, 2008 at 12:26 pm
OK maybe this will help. Insert the first INSERT only. We can assume that reservation is already in the database w/o any problems.
then
If you performed a SELECT with the dates listed for each of the inserts should result in the following:
Insert# Allowed
---------------
1 N - the dates, runtype and runno are the booked already
2 Y - checking in is allowed on the same date another is checking out
3 Y - the dates, runtype and runno are not booked for those dates
4 Y - same as #2 only reversed
5 N - cant book since 11/11 and 11/12 are already booked (overlapping)
6 N - cant book since 11/12 - 11/14 are already booked (overlapping)
The SELECT is what I am trying to write?
November 19, 2008 at 12:28 pm
When you make a reservation it goes in the Reservation Table. That is the only table I need to query against.
After thinking about what you said in your last post that maybe an option but the application is set up to allow the user to see a table of whats available (not by runno but by run type - i.e. how many Large runs are available for a set of dates. So when the user makes a reservation they will select a runtype/runno and dates and at that point it needs to determine if they can or not.
With your method after thinking even more, they could list whats available at that point in time AND let the system just sit there for a point of time while another networked computer makes a reservation for the same dates/run etc... so I guess what I am saying is I think I need to stay with my original idea as they press a 'Reserve' button and at that time I need to verify what they are adding is ok.
November 19, 2008 at 12:36 pm
Different angle of attack. When making a reservation, do you keep trying until you find an available RunNo & RunType? You don't have a screen that returns open RunNo/RunTypes for a given period of time? I'd think this would greatly assist the reservation process, but would not eliminate the need to check while making the reservation. There may be more than one person at a time making reservations.
November 19, 2008 at 12:53 pm
That is correct, they try to make a reservation. The SELECT query would check to see if they can and if they can it can either return something saying they can or go ahead and make the actual reservation(UPDATE). If not, then it would display a message stating the dates/run is already booked and the process would start all over again until they can make a reservation.
I think doing the UPDATE right after the select would be best and i think you would agree on that. The UPDATE only needs to set
RunNo = @RunNo
RunType = @RunType
PetCheckedIn = 'Y'
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply