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

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

Move audience and Access control

So, this will be the last post on move-audience. I am obsessed.
We used move-audience to move tickets between events, and found that the barcodes table is not updated in the move. The barcodes table stores the event code in the brtentity field, which the scanning sp's use to look up the event.
An alternative to this query is to use the company table setting 'Reset print status On Move Audience' which will unprint the moved tickets, so you can re-issue them with valid barcodes.
So, if you use move-audience between events, and also use scanners, use this sql to update the barcodes table :

update barcodes
set brtentitycode = tievent
from
Tickets with(nolock) INNER JOIN
Barcodes ON Tickets.tiCode = Barcodes.brtSourceCode
AND Tickets.tiEvent <> Barcodes.brtEntityCode inner join
events with(nolock) on tievent = evcode
WHERE
(Barcodes.brtSourceTable = 1)
AND (Tickets.tiSaleDate > '1/1/08')
AND (Tickets.tiStatus = 1)
and brtEntityType = 1
and eveventdate > getdate()

Move Audience Audit script

I came across the instance where ticket sales ws confused after a customer called in claiming they had ordered different seats than what was mailed to them. the reason their seats had changed was due to a stage extension on the event, where the audience was moved using the move-audience feature in PE administration. I figured that a good solution to this problem would be to insert a remark for each seat affected on the patron's account, since there is no other way to see move-audience history with PE . The following stored procedure is what I use to do this. I have it set up to stuff remarks every night for seat moves that occured in the past 24 hours:


create procedure moa_create_moveaudience_remarks as

declare @fromdate datetime
set @fromdate = getdate() - 1

select
identity(int) as id,
masl.maslrecordid,
maslupdatedate,
uscode,
slpmachinename,
tix.cltcode,
tix.cltgroupcode,
tix.type,
evfrom.eveventdate as fromevdate,
shfrom.shdescr as fromshow,
arfrom.arname as fromar,
ahfrom.ahshortarname as fromsec,
ahfrom.ahline as fromrow,
ahfrom.ahchair as fromseat,
evto.eveventdate as toevdate,
shto.shdescr as toshow,
arto.arname as toar,
ahto.ahshortarname as tosec,
ahto.ahline as torow,
ahto.ahchair as toseat
into #m
from
MoveAudienceSeatsLog masl with(nolock)
inner join
(select ticode as tcode, timailinglist as cltcode, ticlientgroupcode as cltgroupcode, 'Single' as type from tickets with(nolock)
union
select tiscode, prosubsnum, procustnum, 'Subs' from ticketssubscription with(nolock)
inner join subsprogram with(nolock) on prorecnum = tissubsprogram
union
select tbxcode, tbxmailcustomer, tbxclientgroupcode, 'Reservation' from ticketbaseextra with(nolock) )
as tix on masl.maslticketcode = tix.tcode inner join
areachair ahfrom with(nolock) on masl.masloldseat = ahfrom.ahchaircode inner join
areachair ahto with(nolock) on masl.maslnewseat = ahto.ahchaircode inner join
areas arfrom with(nolock) on arfrom.arcode = masl.masloldareacode inner join
areas arto with(nolock) on arto.arcode = masl.maslnewareacode inner join
events evfrom with(nolock) on masl.masloldeventcode = evfrom.evcode inner join
shows shfrom with(nolock) on shfrom.shcode = evfrom.evshow inner join
events evto with(nolock) on evto.evcode = masl.maslneweventcode inner join
shows shto with(nolock) on shto.shcode = evto.evshow inner join
users with(nolock) on masluser = usrecid inner join
salespoints with(nolock) on salespoints.slpnumber = masl.maslsalepoint
where maslupdatedate > @fromdate
order by maslrecordid

declare @rmkstartnum int, @rmknewnum int
set @rmkstartnum = (SELECT coLastNumber + 1 FROM Counters WHERE (coName = 'TableCustomerRemarks'))
set @rmknewnum = (select count(*) from #m) + @rmkstartnum
update Counters set coLastNumber = @rmknewnum where (coName = 'TableCustomerRemarks')


insert into moa_pe.dbo.customerremarks
select id + @rmkstartnum , cltgroupcode, cltcode, 0, 53, maslupdatedate,
'From ' + convert(varchar(20),fromevdate,101) + ' ' + left(fromshow,10) + ' To: ' + convert(varchar(20),toevdate,101) + ' - ' + left(toshow,10)
+ char(13) + char(10)
+ 'From: ' + ' ' + fromsec + ' ' + fromrow + ' ' + fromseat + ' to' + char(13) + char(10)
+ 'To: ' + ' ' + tosec + ' ' + torow + ' ' + toseat + ' ' + char(13) + char(10)
+ 'By: ' + Uscode + ' ' + slpmachinename
,
1, 0, 0, '1/1/1900', 1
from #m

--select * from #m

drop table #m


GO



PEO: Recommended Events

This feature can be cumbersome, but here are some ideas to make it easier to read:
1:
When adding anything to this list, ALL shows display in alphabetical order. this is dumb.
to fix:
go to System Setup / Column Rules >> find the entry where name = rcshowcode and table = Recommended
Open the record, and change the "Source" to:

SELECT Shows.shCode, CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName AS shname
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE Events.evDateTime > GETDATE()
GROUP BY Shows.shCode, Shows.shName
ORDER BY min(evdatetime)


Change the SQL SELECT to:

rcShowCode = ISNULL(( SELECT TOP 1 CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE shCode = rcShowCode GROUP BY Shows.shCode, Shows.shName), 0)

Ok. Now you can use the feature.
Go back to recommended events, and add a new show to a category. it should work. bada bing, easy to use, its the way the world should be.
There is also a 'Site level' type of entry, which governs which events show up on the default 'all events' page, the page the users see when they first get to the site. We like to have the next 7 non-rental events on this page at all times. To manage this, I use a nightly sql job to populate the Site Level events.
Here it is:


CREATE PROCEDURE Kustom_recommended AS
--Thats Kustom with a kapital K

--for testing...
--delete from recommended where rctype = 1
--get show list
SELECT
TOP 7
min(Recommended.rcCategoryCode) as cat,
Recommended.rcShowCode as show
into #ev
FROM Recommended INNER JOIN
Shows ON Recommended.rcShowCode = Shows.shCode INNER JOIN
Category ON Recommended.rcCategoryCode = Category.cgCode inner join
events on events.evshowcode = shows.shcode
WHERE
events.evendsaledate > GETDATE()
and Events.evDateTime > GETDATE()
AND Recommended.rcType = 2 --category level entries
AND Category.cgName NOT LIKE '%rental%'
group by Recommended.rcShowCode
ORDER BY min(Events.evDateTime)

--insert into recommended
declare @next int, @show int, @cat int

declare r cursor for select cat, show from #ev
open r
fetch next from r into @cat, @show
while @@fetch_status = 0
begin
if not exists (select * from recommended where rctype = 1 and rcshowcode = @show )
begin
set @next = (SELECT cnValue + 1 FROM Counters WHERE (cnName = 'NewRecommend'))
update counters set cnvalue = @next, cnlastupdate = getdate() where cnname = 'NewRecommend'
insert into recommended select @next, 1, @cat, @show, getdate()
end

fetch next from r into @cat, @show
end
close r
deallocate r
drop table #ev

--clean up recommended table:
delete from --select * from
recommended
where rcshowcode in (SELECT shCode FROM Shows WHERE shMaxDate < GETDATE())
GO


This also will delete old entries that are no longer needed.
Disclaimer: use at your own risk. Always test before using. i am not responsible for anything that may happen to you. Don't run with scissors.

Re: PE is So Slow - CRM Series Tab Improvement

The Series tab in CRM is really slow, so i took a crack at re-writing the select statement. The idea is to only get aggregate data that is required by the Tablesrules table. (Tables Definitions in administration)
It works great on my test environment, but please test yourself. i need lab rats!
In my testing, I found that it was able to load the series tab in 1 second, where it used to take 60. most accounts used to load in 3 seconds, and now load in .5 seconds.

To Implement, replace the If @Action = 'Season' ... section in the GetClientLists stored Procedure with the following:

IF @Action = 'Season'
begin
set nocount on

if @clientcode > 0
set @clientgroupcode = (select top 1 cltgroupcode from clients with(nolock) where cltcode = @clientcode)

create table #output (ProgramCode int, LastTransact int, TransactDate datetime, TransactNum int, Client_Code int, Client varchar(255),
FullName varchar(255), SeasonDescr varchar(100), SeriesDescr varchar(255), HallDescr varchar(100), AreaDescr varchar (100),
RowDescr varchar(10), ColDescr varchar(10), Season_Code int, Season varchar(100), Series_Code int, Series varchar(255),
SeriesType int, SubsType varchar(255), Hall varchar(255), Area varchar(255), Sector varchar(255), Row varchar(10), Col varchar(10),
Price money, Subsidy money, ChairCode int, AreaCode int, Status_Code int, Status varchar(50), Renewstatus varchar(50),
NumOfEvents int, NumOfBonus int, NumOfOptional int, TicketsLeft int, BonusLeft int, OptionalLeft int, PrintCount int, SelectionCode int,
SerialNum varchar(50), OriginalProgram int, OpeningDate datetime, FreezedStatus_code int, EndDate datetime, LastPrintDate datetime )

insert into #output (Programcode, LastTransact,
TransactDate,TransactNum ,Client_Code ,Client ,Fullname ,SeasonDescr ,Seriesdescr ,Halldescr ,areadescr ,rowdescr ,coldescr ,
season_code,season ,Series_code ,Series ,SeriesType ,SubsType ,Hall ,Area ,Sector ,Row ,Col ,Price ,Subsidy ,
chaircode ,areacode ,status_code ,status ,renewstatus ,NumOfEvents ,NumOfBonus ,NumOfOptional ,Ticketsleft ,BonusLeft, OptionalLeft ,PrintCount ,
SelectionCode, SerialNum ,OriginalProgram ,Openingdate ,FreezedStatus_code ,EndDate, lastprintdate )

select
sp.prorecnum,
case when sp.prolasttransact = 0 then sp.prorecnum else sp.prolasttransact end,
sp.protransactdate,
sp.protransactnum,
prosubsnum,
'', '',
sn.ssndescr,
sr.srdescr,
ha.haname,
'', '', '',
sn.ssncode,
sn.ssndescr,
sr.srcode,
sr.srdescr,
sp.proseriestype,
sp.prosubstype,
ha.haname,
'', '', '', '',
sp.proprice,
sp.prosubsidy,
sp.proseatnum,
sp.proarea,
sp.procurrentstatus,
'' as status ,
convert(varchar(10),prorenewstatus) as renewstatus,
sp.pronumofevents,
sp.probonus,
sp.prooptionalevents,
0,0,0,
sp.proprintnum,
sp.proselectioncode,
sp.proserialnumber,
case when sp.prolasttransact = 0 then sp.prorecnum else sp.prolasttransact end,
protransactdate,
Case proLastTransact
When 0 Then IsNull((Select Top 1 eesStatus From ExtendedEntityStatuses with(nolock) Where eesEntityCode = proRecNum And eesEntityType = 1 And eesStatus < 8), 0)
Else IsNull((Select Top 1 eesStatus From ExtendedEntityStatuses with(nolock) Where eesEntityCode = proLastTransact And eesEntityType = 1 And eesStatus < 8), 0)
End,
case when sp.proenddate = '1/1/1900' then
case when sr.srenddate = '1/1/1900' then sn.ssntodate else sr.srenddate end
else sp.proenddate end ,
'1/1/1900'
from subsprogram sp with(nolock) inner join
serieses sr with(nolock) on sp.proseriescode = sr.srcode inner join
seasons sn with(nolock) on ssncode = srseasons left outer join
halls ha on ha.hacode = sr.srhall
where
@clientgroupcode = sp.procustnum and
sp.proCurrentStatus IN (10, 21, 22, 23, 27, 28, 29)
and (sp.proseasoncode = @seasoncode or @seasoncode = 0)
and (sp.prosubsnum = @clientcode or @viewallingroup = 1 )
and dbo.IsRestriction(@IsAccessControl,@UserGroup,10, sr.srCode,@LockAction, sr.srOrgUnit) = 0
ORDER BY proRecNum DESC

update #output set status_code = case renewstatus when '32' then 36 when '30' then 30 else 34 end where enddate < getdate() and status_code = 10

update #output set status = stsdescription from statustype with(nolock) inner join #output on stsstatus = status_code

update #output set renewstatus = stsdescription from statustype with(nolock) inner join #output on stsstatus = convert(int,renewstatus)
where isnumeric(renewstatus) = 1


declare @trseason table (trcolumn varchar(100))

insert into @trseason
SELECT trColumn FROM TablesRules WHERE trTable = 'SeasonTabFields' AND trDisplay = 1

if exists (SELECT * FROM @trseason WHERE trColumn = 'Client')
update #output set client = cltclientname, fullname = cltclientname
from #output inner join clients on clients.cltcode = #output.client_code

if exists (SELECT * FROM @trseason WHERE trColumn = 'area')
update #output set Areadescr = arname , area = arname
from areas inner join #output on areacode = arcode

if exists (SELECT * FROM @trseason WHERE trColumn in ('col','row','sector' ) )
update #output set col = ahchair, row = ahline, sector = ahshortarname, rowdescr = ahline, coldescr = ahchair
from #output inner join areachair with(nolock) on ahchaircode = chaircode

if exists (SELECT * FROM @trseason WHERE trColumn = 'ticketsleft')
update #output set ticketsleft = numofevents - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (810, 811, 812, 813, 814)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram

if exists (SELECT * FROM @trseason WHERE trColumn = 'bonusleft')
update #output set bonusleft = numofbonus - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (820,821)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram

if exists (SELECT * FROM @trseason WHERE trColumn = 'optionalleft')
update #output set optionalleft = numofoptional - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (830,831)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram

if exists (SELECT * FROM @trseason WHERE trColumn = 'lastprintdate')
update #output set lastprintdate = pr.lastprintdate
from #output inner join
(
Select max(praUpdated) as lastprintdate, pradoccode From printingaudit(NOLOCK)
Where praSourceTable=4 and praStatus<>3
group by pradoccode
) as pr on pr.pradoccode = programcode

select ProgramCode ,LastTransact , TransactDate , TransactNum , Client_Code , Client ,
FullName , SeasonDescr , SeriesDescr , HallDescr , AreaDescr ,
RowDescr , ColDescr , Season_Code , Season , Series_Code , Series ,
SeriesType , SubsType , Hall , Area , Sector , Row , Col ,
Price , Subsidy , ChairCode , AreaCode , Status_Code , Status , Renewstatus ,
NumOfEvents , NumOfBonus , NumOfOptional , TicketsLeft , BonusLeft , OptionalLeft , PrintCount , SelectionCode ,
SerialNum , OriginalProgram , OpeningDate , FreezedStatus_code , EndDate , LastPrintDate
from #output

drop table #output

end

Merging Accounts Automatically after RE Integration

So, you ran your integration with RE, you have data flying back and forth between systems, you have people ordering tickets online creating duplicate accounts, and you fins that the integration made a couple thousand duplicates as well. now what? You need to merge lots of accounts, but merging is a bit different post integration. There is a "client merge" utility, but if you are like us, you don't want to merge all johnson's to one super-johnson account. You probably have a hundred or so nit-picky criteria that goes into deciding who to merge, which could never be handled by this utility.
So , here is what you do.
The birthday field is a widely unused field on the back-end of PE, and it turns out, you can auto-merge accounts based on birthdate. Also, the birthday field does not transfer to RE in the integration. The cltbirthdate field in the clients table is what you will use to mark your duplicates. Once duplicates are marked, use the client merge widard to merge the duplicate accounts based on birthdate.
Use something similar to the following code to mark the duplicates:

--safety first
--always use test system for testing
--don't run with scissors
--run at your own risk

declare @mbday datetime, @listsize int
set @mbday = '1/1/2100'
set @listsize = 10

--get mergelist . lowest acct number is always kept
SELECT top 5000
case when ClientCode_1 > ClientCode_2 then ClientCode_2 else ClientCode_1 end as keep,
case when ClientCode_1 < ClientCode_2 then ClientCode_2 else ClientCode_1 end as lose
into #m
FROM MyDuplicateListTable
group by
case when ClientCode_1 > ClientCode_2 then ClientCode_2 else ClientCode_1 end ,
case when ClientCode_1 < ClientCode_2 then ClientCode_2 else ClientCode_1 end


--delete loops
delete from #m
where keep in (select lose from #m)

--already merged recs out
delete from #m where
keep not in (select cltcode from clients with(nolock) )
or lose not in (select cltcode from clients with(nolock) )

--exclude more accounts based on your own criteria ...

--#ml = mergelist
select identity(int) as id, keep, lose into #ml from #m group by keep, lose order by keep
drop table #m
delete from #ml where id > @listsize


--now we go to transfer data that may be lost in the merging process
--copy email address to keep account (lower of the 2 numbers...)
update clients
set clients.cltemail = clt2.cltemail
from clients inner join
#ml on #ml.keep = clients.cltcode inner join
clients clt2 on clt2.cltcode = #ml.lose
where clt2.cltemail like '%_@%_.%__'
and (clients.cltemail like '' or clients.cltupdate < clt2.cltupdate)

--copy peologin to keep account
--existing:
update customerlogin
set customerlogin.cluserlogin = cl2.cluserlogin,
customerlogin.clpassword = cl2.clpassword
from #ml inner join
customerlogin on #ml.keep = customerlogin.clcontact inner join
customerlogin cl2 on cl2.clcontact = #ml.lose
where convert(varchar(30),cl2.clcontact) <> cl2.clpassword -- if pw defaults to acct num, do not copy

--new
insert into customerlogin (clcontact, cluserlogin, clpassword, clnextpwdchange)
select #ml.keep, cl.cluserlogin , cl.clpassword , '1/1/3000'
from #ml inner join
customerlogin cl on #ml.lose = cl.clcontact left outer join
customerlogin cl2 on cl2.clcontact = #ml.keep inner join
(select #ml.keep, max(#ml.lose) as maxloser from #ml inner join customerlogin on clcontact = #ml.lose group by #ml.keep)
as maxlose on maxlose.keep = #ml.keep and maxlose.maxloser = #ml.lose
where cl2.clcontact is null

--restrictions: get union list of 'yes' entries, copy list to both accts
select cltcode, cdprulecode
into #r
from
(
select #ml.keep as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.keep = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.keep as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.lose = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.lose as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.keep = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.lose as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.lose = ClientDataProtection.cdpclientcode
where cdpflag = 1
) as bla
group by cltcode, cdprulecode

--transfer status to existing...
update clientdataprotection set cdpflag = 1, cdpupdate = getdate()
from clientdataprotection inner join #r on #r.cltcode = clientdataprotection.cdpclientcode and #r.cdprulecode = clientdataprotection.cdprulecode

--insert records for recs that do not exist
insert into clientdataprotection
select #r.cdprulecode, #r.cltcode, 1, '1/1/1900', '1/1/3000', 1, getdate()
from #r left outer join
clientdataprotection cdp on cdp.cdpclientcode = #r.cltcode and cdp.cdprulecode = #r.cdprulecode
where cdp.cdpclientcode is null

--end merge data preservation section

--reset button for birthday field:
update clients set cltbirthdate = dateadd(ss,cltcode,'1/1/1900') where cltbirthdate <> dateadd(ss,cltcode,'1/1/1900')

--NOW MARK THE DUPLICATES
--mark cltbirthdate for merging. ac1 and ac1 both get same birthday,
--mark ac1
update clients
set cltbirthdate = dateadd(ss, #ml.keep ,@mbday)
from clients inner join #ml on #ml.keep = cltcode
--mark ac2
update clients
set cltbirthdate = dateadd(ss, #ml.keep ,@mbday)
from clients inner join #ml on #ml.lose = cltcode