I have moved into a role managing a V12 instance of Tessitura that has not moved over to the new standard ticket history table, and has not been customised to include owner/initiator/recipient roles. I'd be interested to see how other organisations who have maintained their custom ticket history tables have added the role data into the table, if anyone is willing to share their sql.
ThanksSarah
Hi
I am just finishing a project where I have completely redesigned ticket history into multiple tables as requests were looking at individual tickets, by order and by performance so decided to optimise it, and as we have been on Tess almost 9 years now the previous imported history (although still being kept) is now redundant which gave me the freedom to change the structure.
I ran a query that returns owner id, initiator id and recipient id then when I load them into the table I add them all as owner first. Then add initiators where the number is not null and is different to the owner id then similar for recipient where recipient number is not null and not the same as either the owner or the initiator. That way each customer who has a stake in the ticket gets acknowledged but don't double count the tickets in a customer's record.
Mark
SQL below for the main proc, the secondary proc builds up the order and performance view for each customer with additional information.
CREATE PROCEDURE [dbo].[LP_UPDATE_TICKET_HISTORY](
@run_type char(1) = 'D'
)
AS
/*****************************************************************************************************
* DESCRIPTION
* This is a localized procedure. It counts only fully paid tickets.
* This could also be changed to include returned tickets if necessary.
*
* It automatically scans for active seasons.
* The run_type parameter allows
* D for daily increment equivalent of skip_days=7
* F to run whole season
* exec #LP_UPDATE_TICKET_HISTORY @run_type='F'
* exec LP_UPDATE_TICKET_HISTORY @run_type='D'
* --------------------------------------------------------------------------------
* Modification History
* Created 2011-03-22 MRidley
* Modified 2011-06-08 Mridley Added primary keys to @lt_ticket_history and @seats
******************************************************************************************************/
SET ANSI_WARNINGS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON
begin
declare @season_loop int,
@end_season_loop int,
@season int,
@loop int,
@max_loop int
declare @seasons as table (
loop_num int,
season_num int
declare @perfs as table(
perf_no int primary key,
prod_season_no int,
time_slot int,
facility int,
season int,
perf_name varchar(30)
declare @seats as table(
row_no int,
owner_no int,
initiator_no int,
recipient_no int,
perf_no int,
order_no int,
zone_no int,
price_type int,
tickets int,
ticket_amount money,
total_amount money,
PRIMARY KEY (perf_no, order_no, zone_no, price_type, row_no)
declare @lt_ticket_history as table(
customer_no int,
cust_type char(1),
PRIMARY KEY (customer_no, perf_no, order_no, zone_no, price_type, ticket_amount)
if @run_type='F'
insert into @seasons
select
rn=row_number()over(order by id desc),
id
from tr_season
end
else
where start_dt <= DATEADD(month, 3, getdate())
and end_dt >= DATEADD(month, -1, GETDATE())
select @season_loop=1,
@end_season_loop=MAX(loop_num)
from @seasons
while @season_loop <= @end_season_loop
select @season = season_num
where loop_num = @season_loop
insert into @perfs
SELECT
perf_no,
p.prod_season_no,
time_slot,
facility_no, -- for NT
ps.season,
i.description
FROM t_perf p (NOLOCK)
JOIN t_prod_season ps (NOLOCK) ON p.prod_season_no = ps.prod_season_no
JOIN t_inventory i (NOLOCK) ON p.perf_no = i.inv_no
WHERE ps.season = @season
ORDER BY p.perf_dt
AND p.perf_dt >= DATEADD(dd, -7, GETDATE())
insert into @seats
rn=row_number()over(
order by x.customer_no, x.initiator_no, x.recipient_no, x.perf_no, x.order_no, x.zone_no, x.price_type, x.tck_amt),
x.*
from (
o.customer_no,
o.initiator_no,
sli.recipient_no,
perf_no = sli.perf_no,
order_no = o.order_no,
zone_no = sli.zone_no,
price_type = sli.price_type,
tickets=count(distinct sli.sli_no),
tck_amt = sli.due_amt,
paid_amt = sum(sli.paid_amt)
FROM @perfs p
JOIN t_sub_lineitem sli (NOLOCK) ON p.perf_no = sli.perf_no
and sli.sli_status IN (3,12)
AND ISNULL(sli.seat_no, 0) > 0
JOIN t_order o (NOLOCK) ON sli.order_no = o.order_no
AND o.customer_no <> 0
JOIN tr_mos m (NOLOCK) ON o.mos = m.id
JOIN t_seat st (NOLOCK) ON sli.seat_no = st.seat_no
JOIN tr_section sect (NOLOCK) ON st.section = sect.id
where p.season=@season
group by o.customer_no,
sli.perf_no,
o.order_no,
sli.zone_no,
sli.price_type,
sli.due_amt
) as x
insert into @lt_ticket_history
select owner_no,
type='O',
order_no,
zone_no,
price_type,
tickets=sum(tickets),
ticket_amount = ticket_amount,
total_amount= sum(total_amount)
from @seats
group by owner_no,
ticket_amount
select initiator_no,
type='I',
where isnull(initiator_no,0)>0
and initiator_no<>owner_no
group by initiator_no,
select recipient_no,
type='R',
where isnull(recipient_no,0)>0
and recipient_no<>initiator_no
and recipient_no<>owner_no
group by recipient_no,
delete LT_TICKET_HISTORY
where perf_no in (select distinct perf_no from @perfs)
insert into LT_TICKET_HISTORY
select distinct h.customer_no,
h.cust_type,
h.perf_no,
h.order_no,
h.zone_no,
h.price_type,
h.tickets,
h.ticket_amount,
h.total_amount,
zg.description,
z.zone_legend,
p.time_slot,
p.facility,
p.season,
p.perf_name,
[dbo].[LFS_GET_PERF_DATETIME](h.perf_no,h.zone_no),
[dbo].[LFS_GET_PERFTYPE](h.perf_no)
from @lt_ticket_history h
join @perfs p on h.perf_no=p.perf_no
join t_zone z (NOLOCK) on h.zone_no=z.zone_no
join TR_ZONE_GROUP zg (NOLOCK) on z.zone_group=zg.id
delete @seats
delete @lt_ticket_history
delete @perfs
select @season_loop = @season_loop + 1
exec LP_UPDATE_TICKET_HISTORY_OTHER