I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
Thank you! I have another question, in the front end, i would like to have a calendar form on it, when user click the date, it would be like 10/12/2006. Also i would like to have time dropdown box, like 8:00am, 9:00am....., How to put together to be the ScheduleDate (10/12/20068:00am), use string then convert to date? In the dropdown box, Is that the format should like 8:00am, or 8am?
I'm facing the same issue and I tried to resolve it using the following codes in the table trigger (After Insert, Update).
IF EXISTS (SELECT * FROM dbo.Appointment a INNER JOIN Inserted i
ON a.AppointmentDate = i.AppointmentDate AND
a.AppointmentNo <> i.AppointmentNo AND
a.PhysicianNo = i.PhysicianNo AND
a.StartTime < i.StartTime AND
a.EndTime >= i.EndTime)
RAISERROR('Physician is Busy at This Time', 16, 1)
But unfortunately it didn't work how I can change my trigger to make sure no appointment start and end times will overlaps with another appointment?
Are you likely to insert many rows at once, or just one or a few at a time? If the latter I personally prefer to call a scalar function in a check constraint. I find the coding easier and it is arguably a little less obscure to others. On the other hand, it is less efficient than a trigger and would run very slowly if many rows are inserted at once.