SQL Help - combining seat location across zones

Arik Beatty (he/him) said:

Does anyone have any suggestions? Any help is appreciated!!

Without knowing your data, it's hard to make specific suggestions, but, in general, I would try to avoid views, at least to start, and the fancy "stuff" and "xml path" code, until/unless necessary. Find the data you want first. 

E.g. this code is more or less what I use to populate ticket history here, in a single order example. Fast code, with no stuffs or XML , and including Zone, seat location, etc..

I had to do something similar for a member report.

If you're using VS_TICKET_HISTORY, I think this would work:

SELECT DISTINCT th.customer_no, th.perf_dt, th.prod_season_no, th.order_no, 
STUFF((
    SELECT ', ' + ps.seat_row + '-' + ps.seat_num
    FROM TX_PERF_SEAT AS ps
    WHERE ps.perf_no = th.perf_no
    AND ps.order_no = th.order_no
    FOR XML PATH('')
    ),1,1,'') as seats
FROM VS_TICKET_HISTORY AS th
-- include sli status condition here

Arik,

If you want a third option, our Development department just LOVES having seating locations output on their customer reports so instead of using the same code in every single report I write, I simply wrote a function to return the seating location based off of the parameters of order and performance numbers.  It has a few things in the way it styles the seating location text that is unique to our hall, but otherwise is completely database ambiguous.  I based it off of the nightly ticket history procedure, so it uses a cursor, but I have never had any real performance issues in using it.  Feel free to peruse if you think it might be useful.

USE [impresario]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[LFS_CP_GET_ORDER_PERF_SEAT_LOC](
	@order_no INT,
	@perf_no INT
	)
RETURNS VARCHAR(MAX)
AS

/***************************************************************************************************
Created:  22 October 2020
By:	John A. Moskal II (JAM 2.0)
Purpose:  Returns the seating locations for a given order based on the performance

This function was created so that I could more easily standardize the seating output for any given
order based on each performance.  I have had to do this a number of times already, and this will
simplify the multi-step process that putting together a readable seat list always ends up being.

Updated 8/23/2022 JAM 2.0 -- Updated to fix the Row/Box issue for Stage Boxes Left and Right.
***************************************************************************************************/
BEGIN

	--Internal declaration
	DECLARE	@seating_loc VARCHAR(MAX) = '', --Removes the ISNULL() issue with NULLs, since ISNULLs would bog it down and NULLs create quirks with the logic, both here and with the @this variables below
			@done CHAR(1) = 'N',
			@c_order_no INT,
			@c_perf_no INT,
			@c_location VARCHAR(500),
			@c_section VARCHAR(500),
			@c_row CHAR(10),
			@c_logical_seat_row INT,
			@c_seat CHAR(10),
			@c_logical_seat_num INT,
			@this_section VARCHAR(500) = '',
			@this_row CHAR(10) = '',
			@this_seat CHAR(10) = ''
	DECLARE	@full_details TABLE (order_no INT,perf_no INT,location VARCHAR(500),section VARCHAR(500),row CHAR(10),logical_seat_row INT,seat CHAR(10),logical_seat_num INT)

	--Initial data
	INSERT INTO @full_details(order_no,perf_no,location,section,row,logical_seat_row,seat,logical_seat_num)
	SELECT	sli.order_no,
			sli.perf_no,
			COALESCE((CASE WHEN sc.print_desc IN ('SBL','SBR') THEN '' ELSE sc.print_desc + ': ' END),sc.short_desc,'') + COALESCE(RTRIM(sc.additional_text) + ' ','') + RTRIM(se.seat_row) + '-' + RTRIM(se.seat_num) AS location,
			COALESCE((CASE WHEN sc.print_desc IN ('SBL','SBR') THEN '' ELSE sc.print_desc + ': ' END),sc.short_desc,'') + COALESCE(RTRIM(sc.additional_text) + ' ','') AS section,
			se.seat_row AS row,
			se.logical_seat_row,
			se.seat_num AS seat,
			se.logical_seat_num
	FROM		T_SUB_LINEITEM sli
		JOIN	T_SEAT se ON sli.seat_no = se.seat_no
		JOIN	TR_SECTION sc ON se.section = sc.id
	WHERE	sli.sli_status IN (2,3,12) --Includes Seated, Unpaid; Seated, Paid and Ticketed, Paid.  So includes paid and unpaid but does not include anything unseated or returned
		AND	sli.order_no = @order_no
		AND	sli.perf_no = @perf_no
	ORDER BY	se.logical_seat_row,
				se.logical_seat_num

	--Create cursor for usage
	DECLARE	full_seating_locations CURSOR FAST_FORWARD FOR
		SELECT	fd.order_no,
				fd.perf_no,
				fd.location,
				fd.section,
				fd.row,
				fd.logical_seat_row,
				fd.seat,
				fd.logical_seat_num
		FROM	@full_details fd
		ORDER BY	fd.logical_seat_row,
					fd.logical_seat_num

	OPEN	full_seating_locations

	--Begin cursor loop
	WHILE	@done = 'N'
		
		BEGIN

			--Fetch statement
			FETCH NEXT FROM full_seating_locations INTO
				@c_order_no,
				@c_perf_no,
				@c_location,
				@c_section,
				@c_row,
				@c_logical_seat_row,
				@c_seat,
				@c_logical_seat_num

			--Catch block to exit the loop
			IF	@@FETCH_STATUS < 0
				BEGIN
					SET	@done = 'Y'
				END

			IF	@done = 'N'
				
				BEGIN

					IF	@c_section = @this_section AND @c_row = @this_row

						BEGIN
							SET	@seating_loc = RTRIM(@seating_loc + ',' + RTRIM(@c_seat))
							SET	@this_seat = @c_seat
						END

					ELSE

						BEGIN

							IF	@c_section = @this_section AND @c_row <> @this_row

								BEGIN
									SET	@seating_loc = LTRIM(@seating_loc + '; ' + RTRIM(@c_row) + '-' + RTRIM(@c_seat))
									SET	@this_row = @c_row
									SET	@this_seat = @c_seat
								END

							ELSE

								BEGIN
							
									IF	LTRIM(RTRIM(@seating_loc)) = '' OR @seating_loc IS NULL
										BEGIN
											SET	@seating_loc = LTRIM(RTRIM(@c_section + RTRIM(@c_row) + '-' + RTRIM(@c_seat)))
										END
									ELSE
										BEGIN
											SET	@seating_loc = LTRIM(RTRIM(@seating_loc + '; ' + @c_section + RTRIM(@c_row) + '-' + RTRIM(@c_seat)))
										END
							
									SET	@this_section = @c_section
									SET	@this_row = @c_row
									SET	@this_seat = @c_seat
						
								END

						END

				END
		END

	CLOSE	full_seating_locations
	DEALLOCATE	full_seating_locations

	RETURN	@seating_loc

END

GO

John A. Moskal II

Thanks Chris!

Thanks Ryan! This got me exactly what we're looking for!

Thanks John - this is super cool!

Very cool. One day I'll get to this point. :)