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
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!
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 "!."
!.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)
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!
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.