Request for help with Extractions!

Try this as your selection criteria for number of unique performances (date hard coded for clarity).  You’ve really constituents attending 100 performances in a year?  That’s terrific, I’m sorry that I can’t test that here!

Select Distinct a.customer_no

 From t_customer a (NOLOCK)

 Where  IsNull(a.inactive, 1) = 1

 AND  Exists (select 1 from vs_tck_hist (NOLOCK) 

Where vs_tck_hist.customer_no = a.customer_no and vs_tck_hist.performance_dt > '12/08/2008'

Having count(distinct convert(char(12), performance_dt) +  matinee_or_evening) >=  100)

Hello everyone

If anyone can help me with extractions, I'd be very grateful. I'm trying to do something quite simple but tying myself up in knots!

I want to pull extractions for RFM analysis and would like to know which constituents have been to more than 100 performances over the last 12 months and have spent more than £600 on tickets in the last 12 months.

Anyone know how (if at all) I can make it pull for the last 12/6/3 months only?

I know I can use Tickets-Total Amount and Number of Unique Performances but these are ever, I think, and I don't know how to restrict the data to a time frame.

Thanks, Alison

Development Manager (IG), The Sage Gateshead (UK)

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

Try this as your selection criteria for number of unique performances (date hard coded for clarity).  You’ve really constituents attending 100 performances in a year?  That’s terrific, I’m sorry that I can’t test that here!

Select Distinct a.customer_no

 From t_customer a (NOLOCK)

 Where  IsNull(a.inactive, 1) = 1

 AND  Exists (select 1 from vs_tck_hist (NOLOCK) 

Where vs_tck_hist.customer_no = a.customer_no and vs_tck_hist.performance_dt > '12/08/2008'

Having count(distinct convert(char(12), performance_dt) +  matinee_or_evening) >=  100)

Hello everyone

If anyone can help me with extractions, I'd be very grateful. I'm trying to do something quite simple but tying myself up in knots!

I want to pull extractions for RFM analysis and would like to know which constituents have been to more than 100 performances over the last 12 months and have spent more than £600 on tickets in the last 12 months.

Anyone know how (if at all) I can make it pull for the last 12/6/3 months only?

I know I can use Tickets-Total Amount and Number of Unique Performances but these are ever, I think, and I don't know how to restrict the data to a time frame.

Thanks, Alison

Development Manager (IG), The Sage Gateshead (UK)

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