4
SELECT TOP N seats.*, eventtickets.eticketprice from seats 
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE ((Seats.seatType ='BOXSEAT') 
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
ORDER BY convert (int, seats.seatSection), seats.seatrow ASC

My query above generates the best available # of N seats, but it will fill up one section and then the rest of that order is put in the next section. I need it to find a new section that has all N seats available. IF a seat is in TicketSales table it is not available. If salecategory is Null then that means ticket has not been SOLD/BLOCK/HOLD so its Available.

It is obviously limited to 6 seats together, but I do have one section which has over 30 seats in one row.

Section 1: 
Row 1: 1-2-3
Row 2: 4-5-6

Section 2:
Row 1: 1-2-3
Row 2: 4-5-6

etc.

I am at my wits end. Been staring at this for 2 days straight and my mind is mush. I am pretty weak at SQL, amazing I got this far above. I think I have gotten close, but no enchilada.

See pics of the 2 main tables attached. I just need the seatprice from eventtickets table so it's not that important.

I thank you for any help you can give me on this.

Ticketsales

Seats

3

If you're happy with what you have already, try starting with a set of the distinct sections, cross joining on that, and then filtering your subquery to only that section. That way, it's like considering each section as if it's its own event.

But... why not consider looking at all the seats and finding blocks where the (N-1)th next one is the actual right number away:

SELECT * FROM
(SELECT seats.*, eventtickets.eticketprice,
    LEAD(seats.SeatID,@N-1) OVER (PARTITION BY seats.seatSection ORDER BY seats.SeatID) AS SeatNAway
from seats 
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE ((Seats.seatType ='BOXSEAT') 
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
) s
WHERE s.SeatId = s.SeatNAway - (@N-1)
ORDER BY convert (int, s.seatSection), s.seatrow ASC

See how you go with this maybe... check that you understand the inner subquery, of course.

0

Community wiki answer:

Even though writing T-SQL of such kind is a good mental exercise, I suggest that you move this business logic to server or client side, where you can process it with language that at least has arrays.

For example, you can get all available seats from the databases to your web app and render them, and then highlight consecutive ones. It would be much more convenient either in this case, or if any changes are to be made to the location of seats and sections.

0

I think I got it in a roundabout way. Thanks for your help and advice. I couldn't figure out the Seatnaway so I had to do something else....

select TOP 3 seats.*, eventtickets.eticketprice from seats
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE seats.seatsection IN (SELECT TOP 1 SEATSECTION FROM seats 
WHERE seattype = 'rbox' AND seatHandicap <> 'HANDICAP'
AND (seatID not in (select seatID from ticketsales WHERE (eventID=6 AND (salecategory = 'SOLD' OR salecategory = 'BLOCK')) OR (eventID=6 AND salecategory = 'HOLD' AND seattimestamp > dateADD(mi, -30, getdate()) ))) 
group by seatsection having count(*) >= 3
ORDER BY convert (int, seatSection)) 
AND (Seats.seatType = 'rbox')
AND (eticketcategory = 'STOCKYARDSRODEO')
AND (seatID not in (select seatID from ticketsales WHERE (eventID=6 AND (salecategory = 'SOLD' OR salecategory = 'BLOCK')) OR (eventID=6 AND salecategory = 'HOLD' AND seattimestamp > dateADD(mi, -30, getdate()) )))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.