We just started implementing Flex Headers, which for the most part have been a delight to all. However, we're getting results with the last perf element that aren't correct. Instead of getting the performance date that most recently precedes the get date, we're getting the very first performance date in a patrons ticket history, resulting in the last perf being as far back as 2003. I believe I need to adjust the SQL code for that element, however I'm not sure exactly what to tweak.
Any help would be appreciated. Thanks.
Good point, Brian! I think if she wanted to restrict to the last attended performance though she would need to add a where clause like where c.perf_dt < GETDATE() yes? The only other thing I would look into is adding some sort of seat status restriction as well to keep out returned tickets or unpaid/unseated guys. Basically, anyone that didn't actually have a valid ticket (and hence could not have attended) but still have that lineitem existing in an order somewhere.From: Brian Wilbur Grundstrom <bounce-brianwilburgrundstrom7163@tessituranetwork.com>Sent: 11/18/2015 11:20:45 PMYou can simply create a view, and put that in tr_query_element.Something like this view will give you the last performance date per person.This will not rely on ticket history being updated. create view LV_LAST_PERFORMANCE asselecta.customer_no,MAX(c.perf_dt) as last_perf_dtfrom T_ORDER ajoin T_LINEITEM b on a.order_no = b.order_nojoin T_PERF c on b.perf_no = c.perf_nogroup by customer_no BRIAN WILBUR GRUNDSTROM | Database AdministratorSHAKESPEARE THEATRE COMPANYRecipient of the 2012 Regional Theatre Tony Award®516 Eighth Street, SE | Washington, DC 20003-2834p 202.547.3230 ext. 2216 | c 917.952.7957bwg@shakespearetheatre.orgwww.shakespearetheatre.orgwww.brianwilbur.com2015|2016 SEASON •• Farber’s SALOMÉ • Porter’s KISS ME, KATE • Hatcher’s THE CRITIC & Stoppard’s THE REAL INSPECTOR HOUND • Shakespeare’s OTHELLO • Orwell’s 1984 • Shakespeare’s THE TAMING OF THE SHREW From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth HawrylukSent: Wednesday, November 18, 2015 5:59 PMTo: Brian W. GrundstromSubject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element Hi Adria,The reason for the updating nightly is because the local procedure pulls from Ticket History (as does the flex header element from this post) and not from live order data. If your organization is like ours (and I believe this is the standard), the procedure that updates ticket history runs once nightly anyway and so having the procedure that pulls this last performance info run more frequently wouldn't do much.If you're super duper desperate and your nightly ticket history procedure doesn't use up too many of your resources you could manually force it to run now followed by the procedure to pull that last performance info you've already set up using the output set cookbook. I've had to run my ticket history updates during the day on occasion and it hasn't caused us issue as long as there isn't much other action going on. As for a more long term solution... you could look at building an element that looks at live order data instead of ticket history although I'm not sure if that's the best way. I've never gone down that path myself. I frequently remind my coworkers how our ticket and package history updates so they aren't surprised by being a day behind. My first concern would be impact on application performance. I have no clue if it would have a big impact or not but it's something that you could try and test. The second concern I would have is making sure you're limiting your query to only look at seated/ticketed/paid orders so you don't get any returned tickets or anything else causing a show to pop up as their last performance if they didn't actually have tickets.Not quite the answer you were looking for but hopefully it's a little bit helpful...From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 11/18/2015 5:22:12 PMBeth, I'm kinda desperate. Do you know if there's a way to pull Last attended performance into an output set without having to create a procedure? I just copied the recipe from the Output set cookbook not realizing that a procedure needs to be run overnight. I need the info now. Do you know of a list of some kind? I see this flex header thing but I've never done a custom flex header before. Any help would be appreciated.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!--View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45400.aspx#45400 or reply to this message Adria Gunter | Subscriptions and Groups Manager STUDIO THEATRE 1501 14TH ST NW WASHINGTON DC 20005 T 202-232-7267, ext. (365) F 202-588-5262 www.studiotheatre.org Subscriptions are online! Reserve your group of 10+ today and enjoy a discount! 2015-2016 SEASON MAIN SERIES CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING | BETWEEN RIVERSIDE AND CRAZY | MOMENT | HEDDA GABLER 2015-2016 SEASON STUDIO X ANIMAL | CONSTELLATIONS 2015-2016 SPECIAL REMOUNT BAD JEWS
Good point, Brian!
I think if she wanted to restrict to the last attended performance though she would need to add a where clause like where c.perf_dt < GETDATE() yes?
The only other thing I would look into is adding some sort of seat status restriction as well to keep out returned tickets or unpaid/unseated guys. Basically, anyone that didn't actually have a valid ticket (and hence could not have attended) but still have that lineitem existing in an order somewhere.
From: Brian Wilbur Grundstrom <bounce-brianwilburgrundstrom7163@tessituranetwork.com>Sent: 11/18/2015 11:20:45 PM
You can simply create a view, and put that in tr_query_element.
Something like this view will give you the last performance date per person.
This will not rely on ticket history being updated.
create view LV_LAST_PERFORMANCE
as
select
a.customer_no,
MAX(c.perf_dt) as last_perf_dt
from T_ORDER a
join T_LINEITEM b on a.order_no = b.order_no
join T_PERF c on b.perf_no = c.perf_no
group by customer_no
BRIAN WILBUR GRUNDSTROM | Database AdministratorSHAKESPEARE THEATRE COMPANYRecipient of the 2012 Regional Theatre Tony Award®516 Eighth Street, SE | Washington, DC 20003-2834p 202.547.3230 ext. 2216 | c 917.952.7957bwg@shakespearetheatre.orgwww.shakespearetheatre.orgwww.brianwilbur.com2015|2016 SEASON •• Farber’s SALOMÉ • Porter’s KISS ME, KATE • Hatcher’s THE CRITIC & Stoppard’s THE REAL INSPECTOR HOUND • Shakespeare’s OTHELLO • Orwell’s 1984 • Shakespeare’s THE TAMING OF THE SHREW
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth HawrylukSent: Wednesday, November 18, 2015 5:59 PMTo: Brian W. GrundstromSubject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
Hi Adria,
The reason for the updating nightly is because the local procedure pulls from Ticket History (as does the flex header element from this post) and not from live order data. If your organization is like ours (and I believe this is the standard), the procedure that updates ticket history runs once nightly anyway and so having the procedure that pulls this last performance info run more frequently wouldn't do much.
If you're super duper desperate and your nightly ticket history procedure doesn't use up too many of your resources you could manually force it to run now followed by the procedure to pull that last performance info you've already set up using the output set cookbook. I've had to run my ticket history updates during the day on occasion and it hasn't caused us issue as long as there isn't much other action going on.
As for a more long term solution... you could look at building an element that looks at live order data instead of ticket history although I'm not sure if that's the best way. I've never gone down that path myself. I frequently remind my coworkers how our ticket and package history updates so they aren't surprised by being a day behind. My first concern would be impact on application performance. I have no clue if it would have a big impact or not but it's something that you could try and test. The second concern I would have is making sure you're limiting your query to only look at seated/ticketed/paid orders so you don't get any returned tickets or anything else causing a show to pop up as their last performance if they didn't actually have tickets.
Not quite the answer you were looking for but hopefully it's a little bit helpful...
From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 11/18/2015 5:22:12 PM
Beth,
I'm kinda desperate. Do you know if there's a way to pull Last attended performance into an output set without having to create a procedure? I just copied the recipe from the Output set cookbook not realizing that a procedure needs to be run overnight. I need the info now. Do you know of a list of some kind? I see this flex header thing but I've never done a custom flex header before.
Any help would be appreciated.
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!
Adria Gunter | Subscriptions and Groups Manager
STUDIO THEATRE
1501 14TH ST NW
WASHINGTON DC 20005
T 202-232-7267, ext. (365)
F 202-588-5262
www.studiotheatre.org
Subscriptions are online! Reserve your group of 10+ today and enjoy a discount!
2015-2016 SEASON MAIN SERIES CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING | BETWEEN RIVERSIDE AND CRAZY | MOMENT | HEDDA GABLER
2015-2016 SEASON STUDIO X ANIMAL | CONSTELLATIONS
2015-2016 SPECIAL REMOUNT BAD JEWS
Yes, it would be created in SQL Server but then it can be referenced by your system tables for output set elements. And that's exactly what i meant about seat status! :)