• Replies 8 replies
  • Subscribers 338 subscribers
  • Views 174 views
  • Users 0 members are here
Related

Query to find people who attended a performance in a production more than once

Tiffany Elliott (Past Member)
$organization

Hi Tess friends,

I'm attempting to write a query to find patrons who have attended a performance in a production season more than once (a repeat buyer) and I'm having a heck of a time finding the table/view that I should be using.

At first, I thought it had to do with 'vs_tkt_hist" as that came up when I was experimenting in list builder, but I don't see that table anywhere in SSMS. 

Does anyone have any recommendations as to what table I should use?  Any idea what I might be doing wrong? Any help would be greatly appreciated. :)

 

Thanks,

Tiffany Evans

Database Coordinator

Huntington Theatre Company

Parents
  • $organization

    Oh wow.

    That did it!!!

     

    Brilliant, Chris!  Thank you!!

     

    Any ideas as to why the view wasn’t working before?  Was I just looking in the wrong place?

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
    Sent: Monday, September 30, 2013 5:23 PM
    To: Tiffany Evans
    Subject: Re: [Tessitura Technical Forum] Query to find people who attended a performance in a production more than once

     

    Tiffany Evans:

    I'm attempting to write a query to find patrons who have attended a performance in a production season more than once (a repeat buyer) and I'm having a heck of a time finding the table/view that I should be using.

    A possible query might be something like:

    select o.customer_no,
        COUNT(distinct li.perf_no) "perf_count"
    from T_SUB_LINEITEM li (nolock)
    join T_ORDER o (nolock)
        on o.order_no = li.order_no
    join T_PERF pr
        on pr.perf_no = li.perf_no
    join T_INVENTORY iv
        on iv.inv_no = pr.prod_season_no
    where li.sli_status in (3,12)
    and o.customer_no > 0
    and pr.prod_season_no = 11053 -- subsititute your prod_season_no
    group by o.customer_no
    having COUNT(distinct li.perf_no) > 1
    order by COUNT(distinct li.perf_no) desc

    From: Tiffany Evans <bounce-tiffanyevans7088@tessituranetwork.com>
    Sent: 9/30/2013 3:34:25 PM

    Hi Tess friends,

    I'm attempting to write a query to find patrons who have attended a performance in a production season more than once (a repeat buyer) and I'm having a heck of a time finding the table/view that I should be using.

    At first, I thought it had to do with 'vs_tkt_hist" as that came up when I was experimenting in list builder, but I don't see that table anywhere in SSMS. 

    Does anyone have any recommendations as to what table I should use?  Any idea what I might be doing wrong? Any help would be greatly appreciated. :)

     

    Thanks,

    Tiffany Evans

    Database Coordinator

    Huntington Theatre Company




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

  • $organization in reply to Tiffany Elliott (Past Member)

    Unknown said:
    Oh wow.
    That did it!!!
     Brilliant, Chris!  Thank you!!

    Great. You're welcome!

    Unknown said:
    Any ideas as to why the view wasn’t working before?  Was I just looking in the wrong place?
     

    Not really sure without knowing more about your tkt history tables; glad the above will work out!

     

Reply Children
No Data