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.