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.
Hi Meredith-
Assuming you have the standard v12 Ticket History implemented, the following code should work well for you to get the date of their next performance:
SELECT TOP 1 CONVERT(VARCHAR, a.perf_dt, 1) FROM [dbo].T_TICKET_HISTORY WHERE a.customer_no = @customer_no AND a.perf_dt >= Getdate() ORDER BY a.perf_dt
We're still in v.11. I get an error result with that code. But it gives me hope for v.12.
The code that comes standard in that element is as follows:
select top 1 CONVERT(varchar,perf_dt,1) + ' ' + perf_name from LVS_TKT_HIST where perf_dt <GETDATE() and customer_no = @customer_no order by perf_dt
Try adding desc to the very end of that.
Thanks Beth! I knew desc was the secret, just couldn't figure out where to put it. Much appreciated.
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.
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...
Ah yes! The Data_Where column is actually the ID number from TR_QUERY_ELEMENT_PARAMETER (which is the next thing you set up according to the cookbook).
For example, in my own TR_QUERY_ELEMENT_PARAMETER I have a Season parameter set up and in the ID column it's value is 4. So back in TR_QUERY_ELEMENT my value is season In (<<p4>>) for anything that reference that season parameter.
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.--View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45396.aspx#45396 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
From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 11/18/2015 5:22:12 PM
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 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
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!
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! :)
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 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/45399.aspx#45399 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
I am guessing that you would enter the following.
Data Select: !.last_perf_dt
Data From: LV_LAST_PERFORMANCE
Data Where: leave empty
Cheers
Sandra
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Adria Gunter Sent: Monday, November 23, 2015 12:29 PM To: Sandra Ashby <sashby@tessituranetwork.com> Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
Thanks for this Brian.
Nick needs to know what goes in the data select column in the TR_QUERY_ELEMENT.
He created the view.
This is what I have so far.
Description: Last Performance
Category: Ticketing
Data Select:???
Data From: ???
Data Where:???
On Wed, Nov 18, 2015 at 6:28 PM, Brian Wilbur Grundstrom <bounce-brianwilburgrundstrom7163@tessituranetwork.com> wrote:
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 Administrator SHAKESPEARE THEATRE COMPANY Recipient of the 2012 Regional Theatre Tony Award® 516 Eighth Street, SE | Washington, DC 20003-2834 p 202.547.3230 ext. 2216 | c 917.952.7957 bwg@shakespearetheatre.org www.shakespearetheatre.org www.brianwilbur.com 2015|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 Hawryluk Sent: Wednesday, November 18, 2015 5:59 PM To: Brian W. Grundstrom Subject: 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! -- View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45399.aspx#45399 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 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!
BRIAN WILBUR GRUNDSTROM | Database Administrator SHAKESPEARE THEATRE COMPANY Recipient of the 2012 Regional Theatre Tony Award® 516 Eighth Street, SE | Washington, DC 20003-2834 p 202.547.3230 ext. 2216 | c 917.952.7957 bwg@shakespearetheatre.org www.shakespearetheatre.org www.brianwilbur.com 2015|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 Hawryluk Sent: Wednesday, November 18, 2015 5:59 PM To: Brian W. Grundstrom Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com> Sent: 11/18/2015 5:22:12 PM
-- View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45399.aspx#45399 or reply to this message
Did Nick grant permissions to imp users to access the view?
Yes this looks good – if you need the name of the last performance the view would need re-written. From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Sandra AshbySent: Tuesday, November 24, 2015 2:48 AMTo: Brian W. GrundstromSubject: RE: [Tessitura Technical Forum] Issue with Flex Header Last Perf element I am guessing that you would enter the following. Data Select: !.last_perf_dtData From: LV_LAST_PERFORMANCEData Where: leave empty CheersSandra From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Adria GunterSent: Monday, November 23, 2015 12:29 PMTo: Sandra Ashby <sashby@tessituranetwork.com>Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element Thanks for this Brian. Nick needs to know what goes in the data select column in the TR_QUERY_ELEMENT.He created the view. This is what I have so far. Description: Last PerformanceCategory: TicketingData Select:???Data From: ???Data Where:??? On Wed, Nov 18, 2015 at 6:28 PM, Brian Wilbur Grundstrom <bounce-brianwilburgrundstrom7163@tessituranetwork.com> wrote: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 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/45399.aspx#45399 or reply to this messageAdria Gunter | Subscriptions and Groups ManagerSTUDIO THEATRE1501 14TH ST NWWASHINGTON DC 20005T 202-232-7267, ext. (365)F 202-588-5262www.studiotheatre.orgSubscriptions 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 GABLER2015-2016 SEASON STUDIO X ANIMAL | CONSTELLATIONS 2015-2016 SPECIAL REMOUNT BAD JEWSThis 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!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/45534.aspx#45534 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
Yes this looks good – if you need the name of the last performance the view would need re-written.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Sandra AshbySent: Tuesday, November 24, 2015 2:48 AMTo: Brian W. GrundstromSubject: RE: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Adria GunterSent: Monday, November 23, 2015 12:29 PMTo: Sandra Ashby <sashby@tessituranetwork.com>Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
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 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/45399.aspx#45399 or reply to this messageAdria Gunter | Subscriptions and Groups ManagerSTUDIO THEATRE1501 14TH ST NWWASHINGTON DC 20005T 202-232-7267, ext. (365)F 202-588-5262www.studiotheatre.orgSubscriptions 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 GABLER2015-2016 SEASON STUDIO X ANIMAL | CONSTELLATIONS 2015-2016 SPECIAL REMOUNT BAD JEWS
--View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45399.aspx#45399 or reply to this message