I have 3 tables:v - vId, vname, maxPpl
e - eId, Id, eName, date, time, price
b - bId, eId, bookDate, tickNo, contactNo
I can add bookings for the show but I can't get anything to work to limit the number per event to the venue limit(tickNo). I have all the other queries I need working but not really even sure where to start. I think I could limit it manually but then it would be pointless having the people limit in that table.
Any help much appreciated!
I think I would probably do:
DECLARE @RetVal int -- Return Value - 1=Full, 0=OK
BEGIN TRANSACTION MyLabel_01
SAVE TRANSACTION MyLabel_02
INSERT INTO booking(bookingId, eventId, bookDate, numberTickets, contactNo)
VALUES (@bookingId, @eventId, @bookDate, @numberTickets, @contactNo)
SELECT @RetVal = CASE WHEN V.maxPpl > SUM(numberTickets) THEN 1 ELSE 0 END
FROM booking AS B
JOIN event AS E
ON E.eventId = B.eventId
JOIN venue AS V
ON V.venueId = E.venueId
WHERE B.eventId = @eventId
GROUP BY B.eventId, V.maxPpl
IF @RetVal = 1 -- Venue is full for this event
BEGIN
ROLLBACK TRANSACTION MyLabel_02
COMMIT TRANSACTION MyLabel_01
END
ELSE
BEGIN
COMMIT TRANSACTION MyLabel_01
END
RETURN @RetVal
Might need to wrap an inner-query if SQL complains about the CASE statement in an aggregate query.
Do you process only one booking at a time, or does it need to be multiple bookings at once?
Btw, event should also include maxPpl, as certain types of events change the capacity of the venue. For example, a concert may have a different number of total seats vs. a football game.
As one booking can be for multiple tickets [numberTickets] would that make a difference?
(I've changed my code from COUNT(*) to SUM(numberTickets) now that I've read the O/P more carefully 
)
Thank you so much for the quick replies! I've been stuck on this all day!! I'll let you know how I get on with that!
Would this not sum the total of tickets sold for all events?
WHERE B.eventId = @eventId
will restrict to a single event (the one for the row being inserted)
I think you'll want to lock the rows for the very short period of time while confirming ticket availability and booking the tickets. Something roughly like this:
--values supplied on entry to ticket code
DECLARE @bookDate date
DECLARE @contactNo varchar(30)
DECLARE @eventId int
DECLARE @numberTickets int
SELECT @eventId = 42, @bookDate = '20160511', @numberTickets = 4, @contactNo = 11
---------------------------------------------------------------------------------------------------
--values provided by ticket code processing itself, no need to pass in
DECLARE @date varchar(10)
DECLARE @ename nvarchar(100)
DECLARE @maxPpl int
DECLARE @return_message nvarchar(4000)
DECLARE @time varchar(10)
DECLARE @venueId int
DECLARE @vname nvarchar(100)
SELECT @venueId = e.venueId, @ename = e.ename, @date = CONVERT(varchar(10), e.date, 101),
@time = RIGHT(CONVERT(varchar(30), e.time, 0), 7), @maxPpl = v.maxPpl, @vname = v.vname
FROM event e
INNER JOIN venue v ON v.venueId = e.venueId
WHERE e.eventId = @eventId
BEGIN TRY
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --"hold" the rows read so the same remaining tickets can't be sold twice.
IF (SELECT SUM(numberTickets) FROM booking b WHERE b.eventId = @eventId) + @numberTickets > @maxPpl
BEGIN
RAISERROR('Max people would be exceeded for "%s" at "%s" on "%s %s", %d additional tickets not currently available.', 16, 1,
@ename, @vname, @date, @time, @numberTickets)
GOTO NoInsert
END --IF
INSERT INTO Booking
SELECT @eventId, @bookDate, @numberTickets, @contactNo
SET @return_message = 'Succesfully booked ' + CAST(@numberTickets AS varchar(5)) + ' tickets to "' + @ename + '" ' +
'at "' + @vname + '" on "' + @date + ' ' + @time + '".'
COMMIT TRANSACTION
NoInsert:
END TRY
BEGIN CATCH
--...error handling here
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTEDCurious to know if that is preferable to just inserting them and rolling back if the venue is / becomes full? My thinking was that during booking the venue won't be full so quicker to assume that the booking will succeed. once it becomes full I would take a different tack - e.g. I would have a "Full" indicator on the Event (also useful so that people don't go to the trouble of filling in a booking form if there is no chance of tickets), or a Trigger that maintained a "Seats available count"
I presume there will be some venues where seats are available, for the number required, but the available seats are not adjacent so the booking will fail (or the user will choose for their party to sit in different locations around the venue) - but maybe that is not relevant.
There are no seat assignments to tickets. Just a set number of tickets available for safety reasons. I have a feeling that both of your examples are a little beyond me, tried playing with both but got nowhere. I am going to have to do some more learning I think. I also think I may be able to get round this problem for now with an extra table of available "seats" linked to events with venueId and number. Any thoughts?
I think that is pointless, and would be complex, unless you need to allocate people to specific seat reservations.
maybe your easiest route would be to hold a total of available-seats in the [venue] table, and disallow any booking where the [numberTickets] would exceed the [venue] [available-seats]. That would not stop two people making a booking,a t the "same time", for the remaining few seats, so you would still have to program for that
I wonder if you could get away with:
INSERT INTO booking(bookingId, eventId, bookDate, numberTickets, contactNo)
SELECT @bookingId, @eventId, @bookDate, @numberTickets, @contactNo
WHERE
(
SELECT V.maxPpl >= SUM(numberTickets) + @numberTickets THEN 1 ELSE 0 END AS [AreSeatsAvailable]
FROM booking AS B
JOIN event AS E
ON E.eventId = B.eventId
JOIN venue AS V
ON V.venueId = E.venueId
WHERE B.eventId = @eventId
GROUP BY B.eventId, V.maxPpl
) = 1
IF @@ROWCOUNT <> 1
BEGIN
.... error handling for "Booking declined, Tickets-required exceeds available-seats"
END
although I'm not sure if that is bullet-proof in preventing simultaneous bookings for the last few seats.
This looks much simpler, I am also not too worried about the chances of 2 people booking filling the event at the same time. Payment is taken afterwards anyway. I have got the code working I think but I'm just not sure where the actual new booking information code goes. I am sorry if this is a really dumb question (I have never come across the @local variables before and not too sure where to SET the infomation). Also can't find much info on the net!
That code still allows you to overbook for the last sale, since it doesn't take the number of tickets in the current booking into consideration (i.e., it assumes that it will be only 1).
It makes perfect sense to keep track of seats separately for many events, since it affects the price paid, and some people only want certain types of seats. I can't imagine you'd be able to sell "cattle seating" for every event.
I don't think there's a super simple way to do this and have it be accurate and robust.
Good point!, thanks I've improved, hopefully!, my earlier post
You can replace them with the actual values if, for example, you are using dynamic SQL. I just used @Variable syntax to show where the values would be placed in the SQL statement.
Thanks for all the help Kristen!! I have now managed to get this working and it's great. Just one last problem. It only works once the SUM(numberTickets) is over 50. For instance if there are 49 tickets sold and the next booking was for 2+ tickets it would take the booking because the SUM is less than 50. (For venue 2). Any suggestions how I could get it to SUM(numbertickets) booked and number in the booking? Thanks
I modified my code,retrospectively, following Sctott's feedback - did you spot that change?
This bit:
SELECT V.maxPpl >= SUM(numberTickets) + @numberTickets ...
Thanks
probably better / neater ways of solving that, but this will do
Change:
WHERE
(
SELECT ...
...
GROUP BY B.eventId, V.maxPpl
) = 1
to
WHERE
COALESCE((
SELECT ...
...
GROUP BY B.eventId, V.maxPpl
), 1) = 1