I'm trying to add a element to an output set that shows the amount paid for the last performance attended. I have the last performance element in the system tables. Where do I add total amount paid for that last performance? I keep getting a datawindow error in the output when attempting to add tck_amt to the SELECT string.
Thanks in advance
Normal 0 false false false EN-US X-NONE X-NONE Once Nick updates the view in SSMS for you, then you can use the original bit below. That should be it. !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 10/7/2016 8:37:32 PMOkay so I'm not sure what this means but if it pulls ticket amt that's great. Where do I put this in an output or is this a SQL Management thing? I still really don't have access to that. Order number isn't important. I just need customer_noperf_dt, perf_name, perf_no, season, ticket amount paid for last performance.On Fri, Oct 7, 2016 at 10:02 AM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:Hey Adria and Nick, Yikes! That seems to run very slow (25 seconds). So, this should give you what you want (4 seconds with identical results). I wasn't sure if you would want order number too, but you can simply un-comment it out if you want it. If you would like me to adjust the original one i can. Please make sure you review your output. USE [impresario]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER View [dbo].[LVS_LAST_PERF]AS select a.customer_no,max(a.perf_dt)as perf_dt,max(a.perf_no)as perf_no,max(perf_name)as perf_name,sum(a.tck_amt)as tck_amt,max(a.season) as season--,max(a.order_no) as order_nofrom VS_TICKET_HISTORY awhere a.perf_dt = ( select max(b.perf_dt) from VS_TICKET_HISTORY b JOIN VRS_SEASON s (NOLOCK) on b.season = s.id where a.customer_no = b.customer_no and b.perf_dt < GETDATE() and s.type not in (0,1) )group by a.customer_noGOFrom: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 10/6/2016 10:23:38 PMSo it looks like the Paid Amount I need isn't even in this column. Lynch2 says this is all that's in the last_perf table:customer_noperf_dtperf_nameperf_noseasonOn Thu, Oct 6, 2016 at 2:45 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:If you are pulling from the ticket history table this should work.From: Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com>Sent: 10/6/2016 2:07:45 PMForgot the "!." !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)--View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50870.aspx#50870 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! 2016-2017 SEASON MAIN SERIES CLOUD 9 |STRAIGHT WHITE MEN | THE HARD PROBLEM | THREE SISTERS | THE FATHER2016-2017 SEASON STUDIO X MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART| NO SISTERS | WIG OUT! --View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50904.aspx#50904 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! 2016-2017 SEASON MAIN SERIES CLOUD 9 |STRAIGHT WHITE MEN | THE HARD PROBLEM | THREE SISTERS | THE FATHER2016-2017 SEASON STUDIO X MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART| NO SISTERS | WIG OUT! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing 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!
Normal 0 false false false EN-US X-NONE X-NONE
Once Nick updates the view in SSMS for you, then you can use the original bit below. That should be it.
!.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)
From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 10/7/2016 8:37:32 PM
Hey Adria and Nick, Yikes! That seems to run very slow (25 seconds). So, this should give you what you want (4 seconds with identical results). I wasn't sure if you would want order number too, but you can simply un-comment it out if you want it. If you would like me to adjust the original one i can. Please make sure you review your output. USE [impresario]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER View [dbo].[LVS_LAST_PERF]AS select a.customer_no,max(a.perf_dt)as perf_dt,max(a.perf_no)as perf_no,max(perf_name)as perf_name,sum(a.tck_amt)as tck_amt,max(a.season) as season--,max(a.order_no) as order_nofrom VS_TICKET_HISTORY awhere a.perf_dt = ( select max(b.perf_dt) from VS_TICKET_HISTORY b JOIN VRS_SEASON s (NOLOCK) on b.season = s.id where a.customer_no = b.customer_no and b.perf_dt < GETDATE() and s.type not in (0,1) )group by a.customer_noGOFrom: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 10/6/2016 10:23:38 PMSo it looks like the Paid Amount I need isn't even in this column. Lynch2 says this is all that's in the last_perf table:customer_noperf_dtperf_nameperf_noseasonOn Thu, Oct 6, 2016 at 2:45 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:If you are pulling from the ticket history table this should work.From: Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com>Sent: 10/6/2016 2:07:45 PMForgot the "!." !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)--View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50870.aspx#50870 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! 2016-2017 SEASON MAIN SERIES CLOUD 9 |STRAIGHT WHITE MEN | THE HARD PROBLEM | THREE SISTERS | THE FATHER2016-2017 SEASON STUDIO X MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART| NO SISTERS | WIG OUT! --View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50904.aspx#50904 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! 2016-2017 SEASON MAIN SERIES CLOUD 9 |STRAIGHT WHITE MEN | THE HARD PROBLEM | THREE SISTERS | THE FATHER2016-2017 SEASON STUDIO X MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART| NO SISTERS | WIG OUT! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing 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!
Hey Adria and Nick, Yikes! That seems to run very slow (25 seconds). So, this should give you what you want (4 seconds with identical results). I wasn't sure if you would want order number too, but you can simply un-comment it out if you want it. If you would like me to adjust the original one i can. Please make sure you review your output.
USE [impresario]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER View [dbo].[LVS_LAST_PERF]AS select a.customer_no,max(a.perf_dt)as perf_dt,max(a.perf_no)as perf_no,max(perf_name)as perf_name,sum(a.tck_amt)as tck_amt,max(a.season) as season--,max(a.order_no) as order_nofrom VS_TICKET_HISTORY awhere a.perf_dt = ( select max(b.perf_dt) from VS_TICKET_HISTORY b JOIN VRS_SEASON s (NOLOCK) on b.season = s.id where a.customer_no = b.customer_no and b.perf_dt < GETDATE() and s.type not in (0,1) )group by a.customer_noGO
From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>Sent: 10/6/2016 10:23:38 PM
If you are pulling from the ticket history table this should work.From: Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com>Sent: 10/6/2016 2:07:45 PMForgot the "!." !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)--View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50870.aspx#50870 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! 2016-2017 SEASON MAIN SERIES CLOUD 9 |STRAIGHT WHITE MEN | THE HARD PROBLEM | THREE SISTERS | THE FATHER2016-2017 SEASON STUDIO X MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART| NO SISTERS | WIG OUT!
If you are pulling from the ticket history table this should work.
From: Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com>Sent: 10/6/2016 2:07:45 PM
Forgot the "!."
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!
2016-2017 SEASON MAIN SERIES CLOUD 9 |STRAIGHT WHITE MEN | THE HARD PROBLEM | THREE SISTERS | THE FATHER
2016-2017 SEASON STUDIO X MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART| NO SISTERS | WIG OUT!