• 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 Reply Children
  • $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