• Replies 20 replies
  • Subscribers 415 subscribers
  • Views 66 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
  • $organization
    So 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_no
    perf_dt
    perf_name
    perf_no
    season



    On 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 PM

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

  • $organization in reply to Adria Gunter

    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]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
     
    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_no
    from VS_TICKET_HISTORY a
    where 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_no


    GO

Reply
  • $organization in reply to Adria Gunter

    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]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
     
    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_no
    from VS_TICKET_HISTORY a
    where 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_no


    GO

Children
No Data