Relationships are complicated.

PE has a relationships feature, which can link clients to each other using relationship types (and reciprocal types...) - Mostly I use these at my place of work to track seat-mates and spouses. If you have a large group of people that like to attend together, but pay separately, this is one way to handle the data.  The problem is that it is difficult to report on this data, since you can end up with any number of structures depending on how the links are entered. Ideally you want something that looks like an asterisk, with one group leader as the center that links to everyone else.  Usually this is not the case - you end up with circles, trees, and other shapes that look like tangles of string. Here is a script I wrote that will take this and put into groups of patrons, using the person with the most connections as the leader (or if it is a circular link, the lowest account number is chosen.)  If one were to run this on facebook, it would put 99% of people in Tom's group (or whoever that default friend dude is) - but these are closed groups of seat-mates, so not everyone is linked. (seven seatmates to Kevin Bacon anyone?)

Anyway- here are the goods - this takes the relationship type as input.  This script assumes the same reciprocal and main relationship type for each link.
declare @rtype int

set @rtype = 5

create table #l (cltcode int, link int, lnkgrp int, lnkgrpleader int)

insert into #l (cltcode,link)
select relcltparentid, relcltrelationid from relationships where relfromtype = @rtype or reltotype = @rtype
union select relcltrelationid , relcltparentid from relationships where relfromtype = @rtype or reltotype = @rtype

update #l set lnkgrp = case when cltcode < link then cltcode else link end
declare @i int
set @i = 0
while (
exists (select 1 from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp)
or exists (select 1 from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp)
) and @i < 10 --limit loop to 10 iterations (degrees of separation).
begin
update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp

update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp
set @i = @i + 1
end

update #l set lnkgrpleader = lm.gpwinner
from #l as l1 inner join
(select l1.cltcode,
(select top 1 cltcode from #l as l2 where l2.lnkgrp = l1.lnkgrp group by cltcode order by count(*) desc, cltcode ) as gpwinner
from #l as l1)
as lm on lm.cltcode = l1.cltcode

create table #gl (acct int , lnkgrp int, isgroupleader bit, lname varchar(999), fname varchar(999))
insert #gl (acct, lnkgrp, isgroupleader)
select cltcode, lnkgrp,
case when cltcode = lnkgrpleader then 1 else 0 end as isgroupleader
from #l group by cltcode, lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end
order by lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end desc, cltcode

update #gl set lname = cltsurname, fname = cltfirstname
from #gl inner join clients on cltcode = acct

--select acct from #gl group by acct having count(*) > 1

select * from #gl

drop table #gl, #l

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



Profiles at a glance!

So, you have probably had to dig around in the profile editing program in PE to find who has access to what. If you have more than a couple profiles this can be time consuming.
I put together a bit of sql which helps greatly when testing new versions (and troubleshooting why someone does not have access to something when you do...)

This will sort all profile data by profile-setting-group, profile-setting, and then by profile:



declare @m table (grp varchar(99), node varchar(99), morder int, trail varchar(256), lastparent varchar(99) )

set nocount on
insert into @m
select menus.mnugroup,mnunode,mnuorder,menus.mnugroup , menus.mnugroup
from menus menus with(nolock) where mnunode not in (select mnugroup from menus menus with(nolock)) and mnuactive = 1 order by menus.mnugroup, mnuorder

while exists (select 1 from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0)
begin
update @m set lastparent = mnugroup, trail = mnugroup + ' > ' + trail
from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0
end


SELECT
Profiles.proDescr,
isnull(m.trail , case when prmformdescr like '%???%'then prmformname else prmformdescr end) as groupname,
case when prbcontroldescr = '' then prbcontrolname else prbControlDescr end as prbControlDescr ,
convert(int,priAvailable) as priAvailable
FROM
ProfileBase ProfileBase with(nolock) INNER JOIN
ProfileInfo ProfileInfo with(nolock) ON ProfileBase.prbFormName = ProfileInfo.priFormName AND ProfileBase.prbControlName = ProfileInfo.priControlName INNER JOIN
Profiles Profiles with(nolock) ON ProfileInfo.priProfile = Profiles.proCode left outer join
@m m on m.node = prbControlname left outer join
profilemenus profilemenus with(nolock) on prmformname = prbformname
WHERE (ProfileBase.prbAvailable = 1)
order by Profiles.proDescr, isnull(m.trail , case when prmformdescr like '%???%' then prmformname else prmformdescr end),
morder, ProfileBase.prbControlDescr


You will have this access mess sorted out in no time at all! maybe even print this out for PCI ocumentation!

List series locks in seat ranges

Aha!
I have rigged this one up using a sonic screwdriver, and it just might work.
This is like the single-event-lock-list rpt in a prev post, except for series locks:


CREATE procedure moa_series_lock_count_list (@series int, @season int, @scheme int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select slsseries, slschair, slsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), slsticktype
from moa_pe..serieslockedseats serieslockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on slschair = ahchaircode
where slsseries > 0 and slsticktype between 2000 and 3000
and slsseries in (select srcode from moa_pe..serieses sr with(nolock)
where (srcode = @series or @series = 0)
and (srseasons = @season or @season = 0)
and (srshowtype = @scheme or @scheme = 0) )
order by slsseries, slschair, slsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, srdescr, arname
from @out as o inner join
moa_pe..colors c on clrstatus = o.ticktype inner join
moa_pe..serieses on srcode = o.event inner join
moa_pe..areas on arcode = o.area
order by srdescr, arname, sec, row, list


GO

Yet More useful lock-reporting.

Ah yes. Now what if you have a lock on one series, that shares events with another series, and there is a lock on the other series? How are you to be master of your locks, let alone the universe, if you have a messy lock situation?
Not sure how that happened, but it's as easy as pi to find them:



create procedure moa_series_locks_overlapping
as
SELECT Serieses.srDescr AS series1, Colors.clrDescr AS lock1, Serieses_1.srDescr AS series2, Colors_1.clrDescr AS lock2, AreaChair.ahShortArName AS sec,
AreaChair.ahLine AS row, AreaChair.ahChair AS seat
FROM
moa_pe..LinkSeriesEvents LinkSeriesEvents with(nolock) INNER JOIN
moa_pe..LinkSeriesEvents LinkSeriesEvents_1 with(nolock) INNER JOIN
moa_pe..SeriesLockedSeats SeriesLockedSeats_1 with(nolock) INNER JOIN
moa_pe..SeriesLockedSeats SeriesLockedSeats with(nolock) ON SeriesLockedSeats_1.slsSeries > SeriesLockedSeats.slsSeries AND
SeriesLockedSeats_1.slsChair = SeriesLockedSeats.slsChair ON LinkSeriesEvents_1.lseSeriesCode = SeriesLockedSeats_1.slsSeries ON
LinkSeriesEvents.lseSeriesCode = SeriesLockedSeats.slsSeries AND LinkSeriesEvents.lseEventCode = LinkSeriesEvents_1.lseEventCode INNER JOIN
moa_pe..Serieses Serieses with(nolock) ON SeriesLockedSeats.slsSeries = Serieses.srCode INNER JOIN
moa_pe..Serieses Serieses_1 with(nolock) ON SeriesLockedSeats_1.slsSeries = Serieses_1.srCode INNER JOIN
moa_pe..Colors Colors with(nolock) ON SeriesLockedSeats.slsTickType = Colors.clrStatus INNER JOIN
moa_pe..Colors Colors_1 with(nolock) ON SeriesLockedSeats_1.slsTickType = Colors_1.clrStatus INNER JOIN
moa_pe..AreaChair AreaChair with(nolock) ON SeriesLockedSeats_1.slsChair = AreaChair.ahChairCode
WHERE serieses_1.srdescr not like '%qrt' and serieses.srdescr not like '%qrt'
group by Serieses.srDescr , Colors.clrDescr , Serieses_1.srDescr , Colors_1.clrDescr , AreaChair.ahShortArName ,
AreaChair.ahLine , AreaChair.ahChair

GO

More lock-reporting tidbits

Now its time for some real fun. a report that does something useful for somone i'm sure. This will allow you to find groups of 2 adjacent locks, singles, n adjacent locks, or just list them all.
It takes parameters for event date range and lock type, and lists out the seat locations of these groups of locks. If you are like us, you have some areas in which the rows run vertical. the section of code 'update ... set vertical = 1 where ...' accounts for these.
In our business, single locks are useless for certain lock types, like whell-chair locks, which need a pair.
There is a 65.36 percent chance that you will find this code useful.



CREATE procedure moa_event_lock_count_list (@event int, @evdatefrom datetime, @evdateto datetime, @showtype int, @locktype int, @cntfilter int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select elsevent, elschair, elsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), elsticktype
from
moa_pe..eventlockedseats eventlockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on elschair = ahchaircode
where
elsticktype between 2000 and 3000
and (elsticktype = @locktype or @locktype = 0)
and elsevent in (select evcode
from moa_pe..events with(nolock) inner join
moa_pe..shows with(nolock) on shcode = evshow
where eveventdate between @evdatefrom and @evdateto
and (shshowtype = @showtype or @showtype = 0) )

order by elsevent, elschair, elsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, eveventdate, left(shdescr,15) as showname, arname
from @out as o inner join
moa_pe..colors c with(nolock) on clrstatus = o.ticktype inner join
moa_pe..events with(nolock) on evcode = o.event inner join
moa_pe..areas with(nolock) on arcode = o.area inner join
moa_pe..shows with(nolock) on shcode = evshow
where (cnt = @cntfilter or @cntfilter = 0)
order by eveventdate, arname, sec, row, list


GO



How did k-9 come up with so many significant digits for a probability of random occurences? It might help that he was in a time machine...

Get a handle on your series Locks!

You have serieses to sell, and many of them. you put locks on the series chart to prevent the single events from selling on those seats, so you can sell the subscriptions with all the same seat. You have a bad rep that took the lock on just one of the events in the series, (or you were the victim of the bug (now fixed) in which the series locks did not propogate if seat-info was on the event seat) and now that seat can't be sold for the subscription without allocating one of the seats to a different seat. annoying, right? With a bit of sql you can generate a list of seats that have been 'pirated' in this way:
The following gets a list of locks that should be there, and checks to see if the locks are there, and if not, the seat status. Arr, matey.


--replace moa_pe.. w/ yourdbname..
--the @inc_stage_ext is an option to include or exclude event-lock type pirates

CREATE PROCEDURE moa_series_lock_pirated_seats
(@season int, @inc_stage_ext bit = 0)

AS
/*
--drop table #sl
declare @season int
set @season = 17
declare @inc_stage_ext bit
set @inc_stage_ext = 0
*/
--get all eventt locks that should be there:
create table #sl (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime, evc int)
create index slec on #sl (e,c)

--declare #sl table (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime)
declare @out table (s int, e int, c int, srname varchar(20), evdate datetime, shname varchar(50), lockuser varchar(50),
ticktype varchar(50), pirate varchar(50), acct int, transact int, piratedate datetime, sec varchar(10), row varchar(5), seat varchar(5), reason varchar(50), lockdate datetime )

insert into #sl (s, e, c, ticktype, ticktypecode , evc)
SELECT
SeriesLockedSeats.slsSeries as s,
LinkSeriesEvents.lseEventCode as e,
SeriesLockedSeats.slsChair as c ,
clrdescr,
slsticktype,
ah2.ahchaircode
FROM
moa_pe.dbo.Serieses Serieses with(nolock) INNER JOIN
moa_pe.dbo.SeriesLockedSeats SeriesLockedSeats with(nolock) ON Serieses.srCode = SeriesLockedSeats.slsSeries INNER JOIN
moa_pe.dbo.LinkSeriesEvents LinkSeriesEvents with(nolock) ON SeriesLockedSeats.slsSeries = LinkSeriesEvents.lseSeriesCode inner join
moa_pe..eventsareas with(nolock) on evaevent = lseeventcode inner join
moa_pe..areachair ah1 with(nolock) on ah1.ahchaircode = slschair inner join
moa_pe..areachair ah2 with(nolock) on ah2.ahparentseatid = ah1.ahparentseatid and ah2.ahareacode = evaarea
inner join moa_pe..colors colors with(nolock) on clrstatus = slsticktype inner join
moa_pe..events with(nolock) on evcode = lseeventcode and evhall = srhall
WHERE (Serieses.srSeasons = @season)
--AND (LinkSeriesEvents.lseIsRequiered = 1)
and slsticktype between 2000 and 3000
order by LinkSeriesEvents.lseEventCode , SeriesLockedSeats.slsChair

delete from #sl
from #sl inner join moa_pe..subsprogram with(nolock) on proseatnum = c and proseriescode = s and procurrentstatus = 10 and proseasoncode = @season

update #sl set lockuser = uscode ,lockdate = slhdatelocked
from #sl as sl
inner JOIN
moa_pe..SeatLockHistory SeatLockHistory with(nolock) ON sl.s = SeatLockHistory.slhEventCode AND
sl.c = SeatLockHistory.slhSeatCode AND sl.ticktypecode = SeatLockHistory.slhTickType
and slhlocktype = 3
left outer join
moa_pe..users users with(nolock) on usrecid = slhuserlocked

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tiactualsaleddate,
uscode,
timailinglist,
titransactnum,
'Sold as single ticket',
lockdate
from #sl as sl inner join
moa_pe..tickets t with(nolock) on tievent = sl.e and tichair = sl.evc and tistatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tishift and sfactiontype = 0 left outer join
moa_pe..users with(nolock) on usrecid = sfuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tisactualsaledate,
uscode,
prosubsnum,
tistransactnum,
'Sold as subscription ticket',
lockdate
from #sl as sl inner join
moa_pe..ticketssubscription t with(nolock) on tisevent = sl.e and tischair = sl.evc and tisstatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tisshift and sfactiontype = 0 inner join
moa_pe..subsprogram with(nolock) on case when prolasttransact > 0 then prolasttransact else prorecnum end = tissubsprogram and procurrentstatus = 10 left outer join
moa_pe..users with(nolock) on usrecid = sfuser


insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tibtimestamp,
uscode,
0,
0,
'Event Lock-stage ext',
lockdate
from #sl as sl inner join
moa_pe..ticketbase t with(nolock) on tibevent = sl.e and tibchair = sl.evc and tibticktype = 2 left outer join
moa_pe..users with(nolock) on usrecid = tibuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tbxactualactiondate,
uscode,
tbxmailcustomer,
tbxtransactnum,
'Sold as Reservation',
lockdate
from #sl as sl inner join
moa_pe..ticketbaseextra t with(nolock) on tbxevent = sl.e and tbxchair = sl.evc and tbxstatus = 1 left outer join
moa_pe..users with(nolock) on usrecid = tbxuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select sl.s,sl.e,sl.c, sl.ticktype, sl.lockuser,
'1/1/1900',
isnull(srdescr,''),
0,
0,
'Locked as: ' + clrdescr,
sl.lockdate
from #sl as sl inner join
moa_pe..eventlockedseats els with(nolock) on elsevent = sl.e and elschair = sl.evc and elsticktype <> sl.ticktypecode and elsticktype between 2000 and 3000 left outer join
@out o on sl.c = o.c and sl.e = o.e and sl.s = o.s inner join
moa_pe..colors with(nolock) on clrstatus = elsticktype left outer join
moa_pe..serieses sr with(nolock) on srcode = elsseries
where o.e is null

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s.s,s.e,s.c, s.ticktype, s.lockuser, '1/1/1900', '', 0, 0, 'Not locked on event', s.lockdate
from #sl s left outer join
@out o on s.c = o.c and s.e = o.e left outer join
moa_pe..eventlockedseats on elsevent = s.e and elschair = s.evc and elsticktype = s.ticktypecode
where elsevent is null and o.e is null

update @out
set
srname = left(srdescr,20),
evdate = eveventdate,
shname = left(shdescr,20),
sec = ahshortarname,
row = ahline,
seat = ahchair
from @out inner join
moa_pe..serieses on srcode = s inner join
moa_pe..areachair on ahchaircode = c inner join
moa_pe..events on evcode = e inner join
moa_pe..shows on shcode = evshow

select * from @out
where (@inc_stage_ext = 1 or reason not like '%stage ext%')
order by srname, sec, row, seat

drop table #sl

GO