December 16, 2008 at 9:32 pm
I want a stored procedure for booking a conference hall.
we are taking the from date and to date from the user .Need to show the available timings for that particular day.
thanks
December 16, 2008 at 9:37 pm
I want a stored procedure for booking a conference hall.
we are taking the from date and to date from the user .Need to show the available timings for that particular day.
How can one write a stored proc from the information you've shared?
Can you share base tables structures pls?
December 16, 2008 at 9:49 pm
the tables we have used are
CONFERENCEMASTER
CONFFERENCEID int IDENTITY(1,1) NOT NULL,
CONFFERENCENAME varchar](20)
LOCATIONID int
ERP_CONFFERENCEAVAILABLE
CAID int,
CONFFERENCEID int,
CITYID int,
BOOKINGDATE datetime,
FROMTIME varchar(10)
TOTIME varchar(10)
AVAILABILITY char(1) DEFAULT ('A'),
REASON varchar(100) ,
FROMDATE datetime,
TODATE datetime,
NOOFMEMBERS int,
i want to find the avilable time for booking a conference hall
December 16, 2008 at 10:14 pm
there are too many doubts. First of all, is there only one conference hall? If not, Is there a relation between citiID and LocationID? What are you trying to store in the table CONFERENCEMASTER?
--Assumption - There is only one conference Hall which can be reserved by only one group at a time
--I'm not using FROMTIME and TOTIME columns as these can be stored in FROMDATE and TODATE
--ERP_CONFFERENCEAVAILABLE table contains all booking entries
create procedure FindAvailability(RequiredFromTime as datetime, RequiredToTime as datetime)
AS
If (
SELECT Count(*) from ERP_CONFFERENCEAVAILABLE
where AVAILABILITY <>'A'
and FromDate between requiredFromTime and RequiredToTime -- Check if the RequiredFromTime is already reserved
and TODATE between requiredFromTime and RequiredToTime -- Check if the RequiredToTime is already reserved
) =0
Begin
Print "Available"
END
ELSE
PRINT "Not Available"
---------------------------
I guess if you elaborate more, a better solution can be written.(in fact a better table structure can be written based on the requirement) followed by actual queries..
December 16, 2008 at 10:35 pm
yes,
for a city we have few locations.i.e. they are related.
my problem is for a particular day there may be many slots for booking the hall . i also want to show the available slots for a particular day.
thanks
December 17, 2008 at 5:56 am
How are the slots defined? Is each one an hour? You can simply write a query that uses a parameter for the particular meeting hall and a parameter for the date and it will show the times that are registered. Use the front-end to arrange the display and fill in the gaps.
Or, let's assume there are 7 possible slots in a day 9-10, 10-11, 11-12, 1-2, 2-3,3-4, 4-5. You can number the slots 1-7 and, if you want put them in a look up table, let's call it timeslot.
Now you write a query with a left join between the timeslot and the booked table that returns nulls for missing times like this:
SELECT ts.TimeDesc
,b.Date
,b.Hall
FROM dbo.TimeSlot AS ts
LEFT JOIN Booking AS b
ON ts.TimeSlotId = b.TimeSlotId
WHERE b.Date = @Date
AND b.Hall = @Hall
You could even do this without a table by defining the TimeSlots as a CTE or a sub-query that groups time across a day. It's more work, but less structure to maintain.
However, the requirements are still a bit vague. Remember, the TSQL is supposed to retrieve data, not supply formatting so to get a full display of a day of timeslots, etc., you should rely on application code using the database as a storage and retrieval mechanism.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply