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
Unknown said: 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.
I created new SSRS Ticket and Package history tabs here at the Guthrie, with corresponding custom tables, views, etc., for v12, which include O/I/R role data, as well as several columns specific to our legacy data. Happy to share; please contact me offline if interested.
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. Thanks Sarah This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Thanks Sarah
Thanks everyone. Ken, look forward to seeing what you've got when you're back in town.
CheersSarah
Hi Sarah
I've posted the update script in my Files area here.
(plus our local ticket history table definition for v12, so you can see what it refers to)
There's a couple of local functions called in there - I presume your local tixhist won't need them, but I can add them in if you'd like to see what they do.
This was made in testing for v12.1 - It works for that version. It will need a small mod for v12.5, IIRC - that's what we're going to migrate to (real soon now...)
Ken
Unknown said: This was made in testing for v12.1 - It works for that version. It will need a small mod for v12.5, IIRC - that's what we're going to migrate to (real soon now...)
I'm curious - what mods to ticket history tables/tabs/etc. are needed specifically for v12.5?
Hi Chris
The logic I used creating our new LT_TICKET_HISTORY table for v12.1 was to take the new standard Tess ticket history table as a starting point, and tack our extra local fields on to the end - and then write the update script to populate the fields inherited from the standard version in the same way that Tess populates them - so that our table would be as similar to the Tess table as possible (a superset, in fact) and the diffs would be really clear.
In 12.5, there is a new field added to the Tess standard table, [rule_id] [INT] NULL, which holds a reference to the Rule which may have been activated in setting the price for that item (SLI, I suppose, actually). So to keep maximally consistent, I'll need to add that one into our local table as well, and borrow the appropriate code to populate it.
That's the only change, AFAIK - I haven't looked at that in detail yet, having been on leave for the last month, but I assume it'll be fairly straightforward - just grab the T_SUB_LINEITEM.rule_id value along with the other stuff, and then modify the custom screen stuff to show it (presumably translated into its description); and create List Elements and Output Set Elements to match.
Hi, Ken
Unknown said: The logic I used creating our new LT_TICKET_HISTORY table for v12.1 was to take the new standard Tess ticket history table as a starting point, and tack our extra local fields on to the end - and then write the update script to populate the fields inherited from the standard version in the same way that Tess populates them - so that our table would be as similar to the Tess table as possible (a superset, in fact) and the diffs would be really clear.
Great, I did the same: literally started with a "script table as CREATE to" and added my custom columns.
Unknown said: In 12.5, there is a new field added to the Tess standard table, [rule_id] [INT] NULL, which holds a reference to the Rule which may have been activated in setting the price for that item (SLI, I suppose, actually). So to keep maximally consistent, I'll need to add that one into our local table as well, and borrow the appropriate code to populate it.
Good to know. I'll definitely add column and code for that as well.
Thanks for the details...