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...