• 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

    Unknown said:

    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

Reply
  • $organization

    Unknown said:

    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

Children
No Data