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...