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)
Actually BETWEEN performance dates looks more promising as there are performance dates into the future. Here’s a shot at tickets total amount:
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 between '12/08/2008' and '12/08/2009'
Having sum(tck_amt * num_seats) >= 600)
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Alison Marley Sent: Thursday, December 10, 2009 1:07 PM To: Warren Evans Subject: [Tessitura Development Forum] Request for help with Extractions!
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!
I'm trying to do something similar to this only I want 5 or more unique performances made up of all perfs in one season and two perfs in another season. So I need to put an or in there somewhere. Something is wrong though because I'm not getting the right results. Can anyone help? This is what I have:
Select
Distinct a.customer_no
Where
h.customer_no = a.customer_no AND
performance_name
in (SELECT h.performance_name FROM dbo.VS_TCK_HIST WHERE h.performance_name IN ('fld10rj', 'flw10de'))
HAVING
count(distinct convert(char(12), performance_dt) + matinee_or_evening) BETWEEN 5 AND 99 ) OR
OR
EXISTS (select 1 from vs_tck_hist h (NOLOCK)
h.customer_no = a.customer_no AND h
h
.season = 144
count(distinct convert(char(12), performance_dt) + matinee_or_evening) BETWEEN 5 AND 99
)
Doesn't your block here:
and exists (...) or exists (...)
need enclosing parens like
and (exists (...) or exists (...))
?