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

vs_tkt_hist is a view and not a table.  In SMSS, expand Views and you should see it.

A view or table can be used in a Select statement.

You can also try the table LT_TKT_HIST.

Good Luck

Wendell Baskin

Bass Hall – Fort Worth

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

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!

Oh, and here’s what I got so far from the manual edit in list builder…

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

JOIN vs_tck_hist f (Nolock) ON f.customer_no = a.customer_no

JOIN (select bb.customer_no from vs_tkt_hist aa (nolock) JOIN V_CUSTOMER_WITH_HOUSEHOLD bb ON aa.customer_no = bb.expanded_customer_no where 1=1 group by bb.customer_no having count(distinct perf_no) >=  2) as e ON a.customer_no = e.customer_no

Where  IsNull(a.inactive, 1) = 1

 AND f.season in (67)

AND f.performance_name like 'BUT%'

AND f.performance_dt between  '2013/09/07' And '2013/10/20 23:59:59'

Thanks. J

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

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!

Thanks Wendell,

I think the problem is, there is no vs_tkt_hist anymore…

I also don’t see the table LT_TKT_HIST.

I only see vs_tck_hist and it doesn’t have a column for perf_no, only performance_name and performance_dt.

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

Thanks for your help, Gawain.

It’s so strange…

When I see the manual edit in List builder, it says vs_tkt_hist as the view to reference.

However, in the system table TR_QUERY_ELEMENT, I can see VS_TCK_HIST and it’s in the data from column for the Tickets-Number of Unique Perfs (which is what I would use to see how many unique performances they saw).

I think the query I just got from Chris works well.  Thank you all for your help. J

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

Hi Tiffany,

Do you mean the view VS_TCK_HIST?  I'm afraid Tessitura doesn't have the best record of consistency with naming/abbreviating conventions...

--Gawain