Series House List

You want to see the seats that have been sold in a series, with a few details. Maybe even see the seats that have been sold on a linked series (sharing a required event). Well, now you can see the series nest all at once in a list format. You will still no be able to see the universe in its entirety unless you look into the tardis, though.

create procedure moa_series_houselist (@s int, @show_overlap bit)
as

/*
declare @s int, @show_overlap bit
set @s = 775
set @show_overlap = 1
*/

create table #srhl (
chaircode int primary key ,
sec varchar(99),
row varchar(99),
seat varchar(99),
account int,
transact int,
pricetype varchar(99),
pricelevel varchar(99),
cltname varchar(99),
reservation_type varchar(99),
series varchar(99),
lock_info varchar(99)
)

declare @areas table (arcode int)

insert #srhl (chaircode,account ,transact ,reservation_type ,series, pricetype, pricelevel )
select proseatnum, prosubsnum, protransactnum, 'Seated', srdescr, sbtdescr, tadescr
from moa_pe..subsprogram inner join
moa_pe..serieses on srcode = proseriescode
inner join moa_pe..subscriptiontype on sbtcode = prosubstype
inner join moa_pe..tariff on tacode = propricelevel
where procurrentstatus = 10 and proseriescode = @s

if @show_overlap = 1
begin
insert @areas select sraarea from moa_pe..seriesareas where sraseries = @s

insert #srhl (chaircode,account ,transact ,reservation_type ,series, pricetype, pricelevel )
select a2.ahchaircode, prosubsnum, protransactnum, 'Seated - other series.', srdescr, sbtdescr, tadescr
from moa_pe..subsprogram inner join
moa_pe..serieses on srcode = proseriescode
inner join moa_pe..subscriptiontype on sbtcode = prosubstype
inner join moa_pe..tariff on tacode = propricelevel
inner join moa_pe..areachair a1 on proseatnum = ahchaircode
inner join moa_pe..areachair a2 on a1.ahparentseatid = a2.ahparentseatid
inner join @areas ar on ar.arcode = a2.ahareacode
where procurrentstatus = 10
and proseriescode in
(select l1.lseseriescode
from moa_pe..linkseriesevents l1
inner join moa_pe..linkseriesevents l2 on l1.lseeventcode = l2.lseeventcode
and l1.lseIsRequiered = 1 and l2.lseIsRequiered = 1
and l2.lseseriescode = @s
and l1.lseseriescode <> @s
)
and a2.ahchaircode not in (select chaircode from #srhl) --should not happen, but exclude anyway.
end

update #srhl set sec = ahshortarname, row = ahline, seat = ahchair
from #srhl inner join moa_pe..areachair on ahchaircode = chaircode

update #srhl set lock_info = isnull(clrdescr,'')
from #srhl
left outer join moa_pe..serieslockedseats on slsseries = @s and slschair = chaircode
left outer join moa_pe..colors on slsticktype = clrstatus

update #srhl set cltname = isnull(cltclientname,'?')
from #srhl left outer join moa_pe..clients on cltcode = account

select #srhl.*, ss.id as sortseatid, sr.id as sortrowid
from #srhl
left outer join sortseat ss on seatname = seat
left outer join sortrow as sr on rowname = row

drop table #srhl

Think Inside The Box

If you are using Packages and coupons, you probably have found that it can be difficult to see what is inside the package at a glance. This bit of sql will compress a package to a 2D dataset for easy viewing. Note - this will expand a show list to a list of events attached, but it will not do calculations on time-limitations. It only lists time limitations.

**updated 9/14/11 - old code had incorrect link from limitsetslink to packageitems.


declare @packageid int
set @packageid = 4

declare @item_type as table (code int, descr varchar(99))
insert @item_type (code, descr)
select 1,'Event'
union select 3,'Merchandise'
union select 4,'Series'
union select 5,'Memberships'
union select 6,'Events'
union select 7,'Shows'

declare @limittype as table (code int, descr varchar(99), source varchar(99))

insert @limittype (code, descr, source)
select 9,'Pricetype', 'pricetypes'
union select 10,'Subs Price Type', 'subscriptiontype'
union select 2,'Shows', 'shows'
union select 3,'Halls', 'halls'
union select 7,'Merchandise', 'merchandiseitems'
union select 8,'Price Levels', 'tariff'
union select 14,'Time Limitations', 'timelimitations'
union select 4,'Areas', 'areas'
union select 6,'Series', 'serieses'


declare @benefit_type table (code int, descr varchar(99))
insert @benefit_type (code, descr)
select 0,'No Benefit (qualifier)'
union select 1,'Nominal'
union select 2,'Percent'
union select 3,'Free Item'

declare @timelimitcat as table (code int, descr varchar(99))
insert @timelimitcat (code, descr)
select 1, 'Dates+Hours'
union select 2,'Days of Week + Hours'
union select 3,'Hours'

--select * from @item_type
--select * from @limittype
--select * from @benefit_type
--select * from @timelimitcat

SELECT
pgi_code,
pgi_BasketItemType,
isnull(it.descr ,'?') as item_type_descr,
pgi_MinItemsCount, pgi_MaxItemsCount,
pgi_BenefitType,
isnull(bt.descr,'?') as benefit_type_descr,
pgi_BenefitValue,
0 as pgi_PriceTypeForCommission,
'' as pricetype_for_commission,
lsl_LimitType,
isnull(limittype.descr,'?') as limit_type_descr,
lsl_LimitID,
lsl_AvailabilityType,
replicate(' ',50) as limit_descr,
isnull(eveventdate,'1/1/1900') as evdate
into #pkg
FROM
PKG_PackagesItems PKG_PackagesItems left outer JOIN
LimitSetsLink ON pgi_limitset = lsl_LimitSetID
left outer join @limittype as limittype on limittype.code = lsl_LimitType
left outer join @benefit_type as bt on bt.code = pgi_BenefitType
left outer join @item_type as it on it.code = pgi_BasketItemType
left outer join events on evshow = lsl_LimitID and lsl_limittype = 2
WHERE (pgi_Package = @packageid)
order by pgi_Package, pgi_code

update #pkg set limit_descr = 'All' where lsl_limittype in (10,9,2,3,7,8,4,6) and lsl_limitID = 0

update #pkg
set limit_descr = pctdescr
from #pkg
inner join PriceType ON lsl_limitID = pctCode
where lsl_limittype = 9

update #pkg
set limit_descr = sbtdescr
from #pkg
inner join subscriptiontype ON lsl_limitID = sbtCode
where lsl_limittype = 10

update #pkg
set limit_descr = left(shdescr,50)
from #pkg
inner join shows ON lsl_limitID = shcode
where lsl_limittype = 2

update #pkg
set limit_descr = left(haname,50)
from #pkg
inner join halls ON lsl_limitID = hacode
where lsl_limittype = 3

update #pkg
set limit_descr = left(srdescr,50)
from #pkg
inner join serieses ON lsl_limitID = srcode
where lsl_limittype = 6


update #pkg
set limit_descr = left(mitdescr,50)
from #pkg
inner join merchandiseitems ON lsl_limitID = mitcode
where lsl_limittype = 7

update #pkg
set limit_descr = left(tadescr,50)
from #pkg
inner join tariff ON lsl_limitID = tacode
where lsl_limittype = 8

update #pkg
set limit_descr =
case when lsl_availabilitytype = 1 then 'Available for: ' else 'Not Available for: ' end
+ left(ltg_description,50)
from #pkg
inner join LimitTimeGroup ON lsl_limitID = ltg_recordid
where lsl_limittype = 14


select * from #pkg
order by pgi_code, limit_type_descr, evdate, limit_descr

drop table #pkg