Help with a Query Please? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with a Query Please?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-07-29 : 14:37:04

Hi,

Here is my situation...

I have a theatre with 250 tickets - "seats" - for sale.

The "best" seats are the lowered number seats.

Some people have already purchased tickets.

A new customer wants to buy the "best" 6 seats that are together and that are available.

From running the code below, this would be seats 8, 9, 10, 11, 12, & 13.

Is there an SQL Query that could do this for me? Perhaps someone could at least lead in in the right direction So I could figure it out by myself?

SELECT THE "BEST" "X-Number" of SEATS THAT ARE "TOGETHER"

Any help would be appreciated....My code is below....

Thanks,

JB



-- This code generates all the seats and then
-- purchases some. It then returns the situation
-- described above.
CREATE TABLE #Seats (
	ID int IDENTITY (1, 1) PRIMARY KEY ,
	SeatNo int NOT NULL ,
	PerfID int NOT NULL ,
	Status varchar(15) NOT NULL ,
)
GO

SET NOCOUNT ON
DECLARE @Count int
SELECT @Count = 1

WHILE @Count <= 250
	BEGIN
		INSERT #Seats ( SeatNo, PerfID, Status )
		VALUES ( @Count, 2, 'Available' )
		SET @Count = @Count + 1
	END
SET NOCOUNT OFF
GO

SET NOCOUNT ON
DECLARE @Count int
SELECT @Count = 4

WHILE @Count <= 250
	BEGIN
		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 7
	END
SET NOCOUNT OFF
GO

SELECT * FROM #Seats
GO

DROP TABLE #Seats
GO

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

dsdeming

479 Posts

Posted - 2003-07-29 : 14:49:06

Assuming a theater layout as follows:

1 - 2 - 3 - 4 - 5
6 - 7 - 8 - 9 - 10
11 - 12 - 13 - 14 - 15 ... etc.

Are 8 - 11 considered to be together, or would 8 - 10 and 13 be together?

Dennis

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-07-29 : 15:05:57

Hi Dennis,

I thought I would start out easy and just say that any consectutive numbers would be considered together.

Eventually I would have to break the theatre up into section like you describe, but that would use the sql that I'm looking for as well.

Once I have that, I can configure it from there.

JB

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-29 : 15:18:31

This works, but I am going to keep working on it because I don't like a certain part.


DECLARE @ID INT
DECLARE @Status VARCHAR(15)
DECLARE @HowManySeats INT
DECLARE @HowManyInARow INT
DECLARE @PreviousStatus VARCHAR(15)
DECLARE @WhichSeats VARCHAR(100)

SET @HowManyInARow = 0
SET @HowManySeats = 6
SET @ID = 1
SET @PreviousStatus = 'Taken'
SET @WhichSeats = ''

WHILE @HowManyInARow <= @HowManySeats
BEGIN
	IF @ID = 1
	BEGIN
		SELECT @Status = Status FROM #Seats WHERE ID = @ID
	END
	ELSE
	BEGIN
		SELECT @PreviousStatus = Status FROM #Seats WHERE ID = @ID - 1
		SELECT @Status = Status FROM #Seats WHERE ID = @ID
	END

	IF @Status = 'Available' AND @PreviousStatus = 'Taken'
	BEGIN
		SET @HowManyInARow = 1
		SELECT @WhichSeats = CONVERT(VARCHAR(6), @ID)
	END

	IF @Status = 'Available' AND @PreviousStatus = 'Available' AND @HowManyInARow < @HowManySeats
	BEGIN
		SET @HowManyInARow = @HowManyInARow + 1
		SELECT @WhichSeats = @WhichSeats + ', ' + CONVERT(VARCHAR(6), @ID)
	END	

	IF @Status = 'Available' AND @PreviousStatus = 'Available' AND @HowManyInARow = @HowManySeats
	BEGIN
		BREAK
	END	

	IF @Status = 'Taken'
	BEGIN
		SET @WhichSeats = ''
		SET @HowManyInARow = 0
	END
	
	SET @ID = @ID + 1
	
END

PRINT @WhichSeats

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-29 : 15:29:17

I fixed it. I didn't like the fact that I had a BREAK in there.


DECLARE @ID INT
DECLARE @Status VARCHAR(15)
DECLARE @HowManySeats INT
DECLARE @HowManyInARow INT
DECLARE @PreviousStatus VARCHAR(15)
DECLARE @WhichSeats VARCHAR(100)

SET @HowManyInARow = 0
SET @HowManySeats = 6
SET @ID = 1
SET @PreviousStatus = 'Taken'
SET @WhichSeats = ''

WHILE @HowManyInARow < @HowManySeats
BEGIN
	IF @ID = 1
		SELECT @Status = Status FROM #Seats WHERE ID = @ID
	ELSE
	BEGIN
		SELECT @PreviousStatus = Status FROM #Seats WHERE ID = @ID - 1
		SELECT @Status = Status FROM #Seats WHERE ID = @ID
	END

	IF @Status = 'Available' AND @PreviousStatus = 'Taken'
	BEGIN
		SET @HowManyInARow = 1
		SELECT @WhichSeats = CONVERT(VARCHAR(6), @ID)
	END

	IF @Status = 'Available' AND @PreviousStatus = 'Available' AND @HowManyInARow < @HowManySeats
	BEGIN
		SET @HowManyInARow = @HowManyInARow + 1
		SELECT @WhichSeats = @WhichSeats + ', ' + CONVERT(VARCHAR(6), @ID)

	END	

	IF @Status = 'Taken'
	BEGIN
		SET @WhichSeats = ''
		SET @HowManyInARow = 0
	END
	
	SET @ID = @ID + 1
	
END

PRINT @WhichSeats

Tara

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-07-29 : 15:38:56

Hi Tara,

Thanks, that does work. I'm going got need to spend some time to understand it all.

I noticed that you are building and returning a csv string. I think I see where I can simply insert values into a Temp Table so that the results are in rows.

Let me work with this then.....

Thanks,

JB

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-29 : 15:42:57

Yeah a temp table would be much better. I just selected a CSV string because it was easy.

BTW, thanks for this challenge. It was fun coming up with a solution.

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-29 : 16:09:57

JB,
This is a variation of the 'streaks' problem that Jeff Smith handled very well in his article. Search this forum for it.

Jonathan
{0}

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-07-29 : 16:14:11

OK,

I have the new code that would assemble the results into a temp table, however there would be multiple inserts and then deletes as it iterates through the @ID's. This shouldn't be a major problem with only 250 seats. If it were Giants Stadium however.....?

Here is the code I cam up with, with Tara's help of course,...


-- This code generates all the seats and then
-- purchases some. It then returns the situation
-- described above.
CREATE TABLE #Seats (
	ID int IDENTITY (1, 1) PRIMARY KEY ,
	SeatNo int NOT NULL ,
	PerfID int NOT NULL ,
	Status varchar(15) NOT NULL ,
)
GO

SET NOCOUNT ON
DECLARE @Count int
SELECT @Count = 1

WHILE @Count <= 250
	BEGIN
		INSERT #Seats ( SeatNo, PerfID, Status )
		VALUES ( @Count, 2, 'Available' )
		SET @Count = @Count + 1
	END
SET NOCOUNT OFF
GO

SET NOCOUNT ON
DECLARE @Count int
SELECT @Count = 4

WHILE @Count <= 250
	BEGIN
		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 7
	END
SET NOCOUNT OFF
GO

--###################################################
--# HERE IS NEW CODE
--###################################################

CREATE TABLE #AvailableSeats (
	ID int IDENTITY (1, 1) PRIMARY KEY ,
	SeatNo int NOT NULL ,
	PerfID int NOT NULL ,
	Status varchar(15) NOT NULL ,
)



DECLARE @ID INT
DECLARE @Status VARCHAR(15)
DECLARE @HowManySeats INT
DECLARE @HowManyInARow INT
DECLARE @PreviousStatus VARCHAR(15)
DECLARE @WhichSeats VARCHAR(100)

SET @HowManyInARow = 0
SET @HowManySeats = 6
SET @ID = 1
SET @PreviousStatus = 'Taken'
SET @WhichSeats = ''

WHILE @HowManyInARow < @HowManySeats
BEGIN

	IF @ID = 1
		BEGIN
			SELECT @Status = Status FROM #Seats WHERE ID = @ID
		END	
	ELSE
		BEGIN
			SELECT @PreviousStatus = Status FROM #Seats WHERE ID = @ID - 1
			SELECT @Status = Status FROM #Seats WHERE ID = @ID
		END

	IF @Status = 'Available' AND @PreviousStatus = 'Taken'
		BEGIN
			SET @HowManyInARow = 1
			--SELECT @WhichSeats = CONVERT(VARCHAR(6), @ID)
			INSERT #AvailableSeats ( SeatNo, PerfID, Status )
			VALUES ( @ID, 2, 'Available' )
		END

	IF @Status = 'Available' AND @PreviousStatus = 'Available' AND @HowManyInARow < @HowManySeats
		BEGIN
			SET @HowManyInARow = @HowManyInARow + 1
			--SELECT @WhichSeats = @WhichSeats + ', ' + CONVERT(VARCHAR(6), @ID)

			INSERT #AvailableSeats ( SeatNo, PerfID, Status )
			VALUES ( @ID, 2, 'Available' )

		END	

	IF @Status = 'Taken'
		BEGIN
			SET @WhichSeats = ''
			SET @HowManyInARow = 0
			DELETE #AvailableSeats
		END
	
	SET @ID = @ID + 1
	
END


SELECT * FROM #AvailableSeats
DROP TABLE #AvailableSeats

--###################################################
--# END NEW CODE
--###################################################

DROP TABLE #Seats
GO

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-07-29 : 16:15:56

Hi Jonathan,

I just looked at that. I'l give it another look see....

JB

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-29 : 16:20:09

So you know where I was headed-
A seat is either occupied or not. So the "streak" is the # of consecutive occupied or unoccupied seats. Therefore to optimally reserve "n" seats you are looking for a streak of at least "n" length of unoccupied seats such that the lowest seat # in the streak is equal to the lowest seat # of all such streaks.

Jonathan
{0}

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-07-29 : 16:58:38

Hi Jonathan,

Yes, you are right. "Optimal" is further down the road though.....

I used the streaks method and this is what I cam up with....

I'll tell yah, nested derived tables can get just a little confusing......!


-- This code generates all the seats and then
-- purchases some. It then returns the situation
-- described above.
CREATE TABLE #Seats (
	ID int IDENTITY (1, 1) PRIMARY KEY ,
	SeatNo int NOT NULL ,
	PerfID int NOT NULL ,
	Status varchar(15) NOT NULL ,
)
GO

SET NOCOUNT ON
DECLARE @Count int
SELECT @Count = 1

WHILE @Count <= 250
	BEGIN
		INSERT #Seats ( SeatNo, PerfID, Status )
		VALUES ( @Count, 2, 'Available' )
		SET @Count = @Count + 1
	END
SET NOCOUNT OFF
GO

SET NOCOUNT ON
DECLARE @Count int
SELECT @Count = 4

WHILE @Count <= 250
	BEGIN
		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 1

		UPDATE #Seats
		SET Status = 'Taken'
		WHERE SeatNo = @Count

		SELECT @Count = @Count + 7
	END
SET NOCOUNT OFF
GO


--####################################################
--# HERE IS THE "Streaks" CODE
--####################################################
SELECT Min(StartingSeatNum) [Starting Seat], MIN(EndingSeatNum) [Ending Seat]
FROM (

	SELECT Status, 
		MIN(SeatNo) as StartingSeatNum, 
		MAX(SeatNo) as EndingSeatNum, 
		COUNT(*) as SeatsInGroup
	FROM (
		SELECT SeatNo, 
  		Status, 
		  (SELECT COUNT(*) 
  		 FROM #Seats G 
	  	 WHERE G.Status <> GR.Status 
	  	 AND G.SeatNo <= GR.SeatNo) as Streak 
		FROM #Seats GR) As A
	WHERE Status = 'Available'
	GROUP BY Status, Streak
	--ORDER BY Min(SeatNo)
 ) As B
WHERE SeatsInGroup = 6
--####################################################
--# HERE IS THE "Streaks" CODE
--####################################################
GO

DROP TABLE #Seats
GO

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-30 : 04:41:43

You'll probably need to incude PerfID in the join conditions, etc.
If you have an index on Seats(PerfID, SeatNo, Status) then you may find that using MAX(G.SeatNo) instead of COUNT(*) in the streak calculation yields a faster result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources