Does anyone have an idea or a chunk of SQL that will show a membership that is active at the time of a ticket sale. The challange I'm seeing is that there can be multiple memberships in TX_CUST_MEMBERSHIP even for the same membership orginiziation when a ticket is sold. Has anyone solved this riddle?
--Tom
The Joins I'm trying are just not working correctly they add extra ticket records.
select
count (*)
from
dbo.T_SUB_LINEITEM as tsl with (nolock)
left join dbo.t_lineitem as tl with (nolock) on tsl.li_seq_no = tl.li_seq_no
-- Comment the below out and you get a diffrent count
Tom,
This view will give you the most recent membership, with one per customer_no. You are going to have to choose between membership organizations if you want one over the other.
Brian Wilbur GrundstromComposer1453 S Street NWWashington DC 20009213-2600-BWG [294]hm 202-232-3316cell 917-952-7957brian@brianwilbur.comwww.brianwilbur.com
www.brianwilbur.wordpress.com
http://www.imdb.com/name/nm2505389/
CREATE View [dbo].[lv_membership_most_recent]
AS
with last_membership as
(select
a.customer_no,
max(a.expr_dt) as expr_dt,
sum(d.cont_amt) AS total_gift_amount
from tx_cust_membership a (nolock)
left JOIN TX_CONT_MEMB c (NOLOCK) ON a.cust_memb_no = c.cust_memb_no
left join T_CONTRIBUTION d (nolock)on c.cont_ref_no = d.ref_no
where a.expr_dt = (select max(expr_dt) from tx_cust_membership where a.customer_no = customer_no) -- and a.memb_org_no = memb_org_no) want the last membership regardless of memb_org_type
and a.memb_level not in ('LDW')
and a.current_status <> 8 --deactivated
GROUP BY a.customer_no
)
b.cust_memb_no,
b.customer_no,
IsNull((c.fname + ' ' + c.lname), c.lname) as cust_name,
mo.description as memb_org,
b.memb_level,
ml.description as memb_description,
cs.description as current_status,
b.expr_dt,
b.inception_dt,
mc.description as category,
b.ben_provider,
a.total_gift_amount,
b.memb_amt,
b.recog_amt,
b.AVC_amt,
b.memb_org_no,
b.current_status as current_status_no
from last_membership a
join TX_CUST_MEMBERSHIP b on a.customer_no = b.customer_no and a.expr_dt = b.expr_dt and b.current_status <> 8
join T_CUSTOMER c on a.customer_no = c.customer_no
Join t_memb_org mo on b.memb_org_no = mo.memb_org_no
join T_MEMB_LEVEL ml on b.memb_level = ml.memb_level
Join tr_current_status cs on b.current_status = cs.id
Join tr_memb_level_category mc on ml.category = mc.id
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tom BrownSent: Thursday, February 23, 2012 9:31 PMTo: Brian W. GrundstromSubject: [Tessitura Technical Forum] Tickets sold during membership periods
left join dbo.t_order as tor with (nolock) on tl.order_no = tor.order_no
left join [dbo].vxs_cust_membership as M1 with (nolock)
on tor.customer_no = M1.customer_no and tl.create_dt <= M1.expr_dt and tl.create_dt >= M1.create_dt
and M1.memb_org_no = 1
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!
Odd - I thought you couldn't have more than one membership active at the same time for a single membership organization. Maybe I made that up? How would you distinguish between two currently active memberships if you wanted to select one as the membership of interest? Use the max expiration date? Or the max level maybe?
If you have a merge of two accounts there could have been two memberships one on each account at the time of the order.