• Replies 20 replies
  • Subscribers 415 subscribers
  • Views 79 views
  • Users 0 members are here
Related

Last Performance Amount Paid

Adria Gunter
$organization

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

Parents
  • Former Member
    Former Member $organization
    USE [impresario]
    GO

    /****** Object:  View [dbo].[LVS_LAST_PERF]    Script Date: 10/06/2016 16:25:37 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

     
    CREATE View [dbo].[LVS_LAST_PERF]
    AS
     
    select a.customer_no, max(a.perf_dt) as 'perf_dt',
    (select top 1 b.perf_name from VS_TICKET_HISTORY b (NOLOCK)
    JOIN VRS_SEASON s1 (NOLOCK) on b.season = s1.id where b.perf_dt =  max(a.perf_dt) and s1.type not in (0,1) and b.customer_no = a.customer_no) as 'perf_name',
    (select  top 1 c.perf_no from VS_TICKET_HISTORY c (NOLOCK)
    JOIN VRS_SEASON s2 (NOLOCK) on c.season = s2.id where c.perf_dt =  max(a.perf_dt) and s2.type not in (0,1) and c.customer_no = a.customer_no) as 'perf_no',
    (select  top 1 d.season from VS_TICKET_HISTORY d (NOLOCK)
    JOIN VRS_SEASON s3 (NOLOCK) on d.season = s3.id where d.perf_dt =  max(a.perf_dt) and s3.type not in (0,1) and d.customer_no = a.customer_no) as 'season'
    from VS_TICKET_HISTORY a (NOLOCK)
    JOIN VRS_SEASON s (NOLOCK) on a.season = s.id
    where a.perf_dt < GETDATE() and s.type not in (0,1)
    group by customer_no
     

    GO


    On Thu, Oct 6, 2016 at 3:31 PM, Adria Gunter <agunter@studiotheatre.org> wrote:
    Sorry. I think you're asking for something in SQL Managment. I don't have access. Everything I sent before is all I have access to. I'll check with the IT person to see if he can send it to you. 
    Thanks for the help though. I sincerely appreciate it. 

    On Thu, Oct 6, 2016 at 3:14 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    Can you show me your lvs_last_perf view? I can rewrite/add to it quick.

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/6/2016 6:45:34 PM



    Inline image 1

    this what I have in the System Table. 

    DESCRIPTION:
    Last Perf Date


    CATEGORY:
    TICKETING CUSTOM 


    SELECT:
    !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107)  + convert (varchar,!.tck_amt)

    DATA FROM:
    lvs_last_perf

    DATA WHERE:

    Single Row:Checked


    On Thu, Oct 6, 2016 at 2:16 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    Forgot the "!."

    !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/6/2016 5:49:48 PM

    I got this message. 

    Inline image 1

    So I guess there's something that needs to be done on the SQL Management Studio side that I have no access to?

    On Thu, Oct 6, 2016 at 1:30 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    If you want to concatenate it without a space then, yep! Otherwise, if you want a space, see below.

    !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) +'  ' + convert (varchar,tck_amt)

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/6/2016 3:38:12 PM

    Do I tack this on with a "+"? 
    It will now look like !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,tck_amt)

    On Thu, Oct 6, 2016 at 8:57 AM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    Coverting to string error, maybe try the following, convert (varchar,tck_amt) in your select.

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/5/2016 4:54:31 PM

    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



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50854.aspx#50854 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50865.aspx#50865 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50867.aspx#50867 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50874.aspx#50874 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg


Reply
  • Former Member
    Former Member $organization
    USE [impresario]
    GO

    /****** Object:  View [dbo].[LVS_LAST_PERF]    Script Date: 10/06/2016 16:25:37 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

     
    CREATE View [dbo].[LVS_LAST_PERF]
    AS
     
    select a.customer_no, max(a.perf_dt) as 'perf_dt',
    (select top 1 b.perf_name from VS_TICKET_HISTORY b (NOLOCK)
    JOIN VRS_SEASON s1 (NOLOCK) on b.season = s1.id where b.perf_dt =  max(a.perf_dt) and s1.type not in (0,1) and b.customer_no = a.customer_no) as 'perf_name',
    (select  top 1 c.perf_no from VS_TICKET_HISTORY c (NOLOCK)
    JOIN VRS_SEASON s2 (NOLOCK) on c.season = s2.id where c.perf_dt =  max(a.perf_dt) and s2.type not in (0,1) and c.customer_no = a.customer_no) as 'perf_no',
    (select  top 1 d.season from VS_TICKET_HISTORY d (NOLOCK)
    JOIN VRS_SEASON s3 (NOLOCK) on d.season = s3.id where d.perf_dt =  max(a.perf_dt) and s3.type not in (0,1) and d.customer_no = a.customer_no) as 'season'
    from VS_TICKET_HISTORY a (NOLOCK)
    JOIN VRS_SEASON s (NOLOCK) on a.season = s.id
    where a.perf_dt < GETDATE() and s.type not in (0,1)
    group by customer_no
     

    GO


    On Thu, Oct 6, 2016 at 3:31 PM, Adria Gunter <agunter@studiotheatre.org> wrote:
    Sorry. I think you're asking for something in SQL Managment. I don't have access. Everything I sent before is all I have access to. I'll check with the IT person to see if he can send it to you. 
    Thanks for the help though. I sincerely appreciate it. 

    On Thu, Oct 6, 2016 at 3:14 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    Can you show me your lvs_last_perf view? I can rewrite/add to it quick.

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/6/2016 6:45:34 PM



    Inline image 1

    this what I have in the System Table. 

    DESCRIPTION:
    Last Perf Date


    CATEGORY:
    TICKETING CUSTOM 


    SELECT:
    !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107)  + convert (varchar,!.tck_amt)

    DATA FROM:
    lvs_last_perf

    DATA WHERE:

    Single Row:Checked


    On Thu, Oct 6, 2016 at 2:16 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    Forgot the "!."

    !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,!.tck_amt)

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/6/2016 5:49:48 PM

    I got this message. 

    Inline image 1

    So I guess there's something that needs to be done on the SQL Management Studio side that I have no access to?

    On Thu, Oct 6, 2016 at 1:30 PM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    If you want to concatenate it without a space then, yep! Otherwise, if you want a space, see below.

    !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) +'  ' + convert (varchar,tck_amt)

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/6/2016 3:38:12 PM

    Do I tack this on with a "+"? 
    It will now look like !.perf_name + ' on ' + convert(varchar(12), !.perf_dt, 107) + convert (varchar,tck_amt)

    On Thu, Oct 6, 2016 at 8:57 AM, Travis Armbuster <bounce-travisarmbuster9061@tessituranetwork.com> wrote:

    Coverting to string error, maybe try the following, convert (varchar,tck_amt) in your select.

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 10/5/2016 4:54:31 PM

    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



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50854.aspx#50854 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50865.aspx#50865 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50867.aspx#50867 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/16856/50874.aspx#50874 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 FATHER

    2016-2017 SEASON   STUDIO X    MOTHERSTRUCK! | I WANNA FUCKING TEAR YOU APART|  NO SISTERS  |  WIG OUT!

    The New Accessible IconFile:Pictograms-nps-accessibility-open captioning.svgFile:Pictograms-nps-accessibility-sign language interpretation.svgFile:Pictograms-nps-accessibility-assistive listening systems.svghttps://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Pictograms-nps-accessibility-audio_description.svg/1000px-Pictograms-nps-accessibility-audio_description.svg.pngFile:Pictograms-nps-accessibility-large print.svg


Children
No Data