Hello,
I have been working on a script that will eventually be a view to show a single line return of the most recent membership for a patron regardless of the status (Active, Pending, Inactive). I have gotten the script narrowed down to almost the right groupings, but I am having trouble with the final steps.
Logic,
1. Give me memberships with the most recent expiration date grouped by customer_no.
2. If more than one row has the same most recent expiration date, then use most recent initiation date.
3. If more than one row has the same most recent Exp date and Initiation date, then use max last update date.
4. If more than one row has the same most recent exp date, initiation date and last update date, then use max member level.
I know I missing a simple syntax or additional grouping somewhere. Any help or optimization ideas are appreciated
Thank you,
Sadie
_________________________________
USE impresario
SELECT DISTINCT
a.customer_no, a.cust_memb_no ,a.memb_org_no ,a.memb_level, a.init_dt, a.expr_dt, a.inception_dt, e.description AS Status_name, b.description AS level_name, a.cur_record,
a.NRR_status, b.memb_level_no, a.last_update_dt, a.last_updated_by
FROM TX_CUST_MEMBERSHIP AS a INNER JOIN
(SELECT customer_no, memb_org_no ,MAX(expr_dt) AS maxExpDate
FROM TX_CUST_MEMBERSHIP
WHERE memb_org_no in (2,3,6)
GROUP BY customer_no, memb_org_no) AS z ON a.customer_no = z.customer_no AND a.expr_dt = z.maxExpDate INNER JOIN
(SELECT customer_no, memb_org_no, MAX(init_dt) AS maxInitDate
GROUP BY customer_no, memb_org_no) AS y ON a.customer_no = y.customer_no AND a.init_dt = y.maxInitDate INNER JOIN
----- up to this point in the code the data appears to be returning accurate, but too many results.
(SELECT customer_no, MAX(last_update_dt) AS maxUpdtDate
GROUP BY customer_no) AS x ON a.customer_no = x.customer_no AND a.last_update_dt = x.maxUpdtDate INNER JOIN
(SELECT customer_no, MAX(memb_level) AS maxMemLev
GROUP BY customer_no) AS v ON a.customer_no = v.customer_no AND a.memb_level = v.maxMemLev INNER JOIN
T_MEMB_LEVEL AS b ON a.memb_org_no = b.memb_org_no AND a.memb_level = b.memb_level INNER JOIN
TR_MEMB_LEVEL_CATEGORY AS c ON b.category = c.id INNER JOIN
TR_CURRENT_STATUS AS e ON a.current_status = e.id INNER JOIN
T_LIST_CONTENTS as List ON List.customer_no = a.customer_no
ORDER BY a.customer_no
Fernando,
Thank you so much! That is exactly what I needed.
You are the best,
SQL window aggregate function ROW_NUMBER() is what I use in these case. This should work.
SELECT b.*
FROM (
SELECT aa.cust_memb_no,
--This will give a membership "rank" per customer using the criteria you listed
memb_rank = ROW_NUMBER() OVER (PARTITION BY aa.customer_no ORDER BY aa.customer_no, aa.expr_dt DESC, aa.init_dt DESC, aa.last_update_dt DESC, aa.memb_level DESC)
FROM TX_CUST_MEMBERSHIP aa
) a
JOIN TX_CUST_MEMBERSHIP b ON b.cust_memb_no = a.cust_memb_no
WHERE a.memb_rank = 1 --Select only top ranked membership per customer
Fernando Margueirat Senior Business Analyst The National Ballet of Canada 470 Queens Quay West Toronto, Ontario M5V 3K4 P: 416 345 9686 x453 F: 416 345 8323
http://national.ballet.ca
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Sadie Prater Sent: Tuesday, July 19, 2016 5:36 PM To: Fernando Margueirat <FMargueirat@national.ballet.ca> Subject: [Tessitura Technical Forum] Last Membership Data for all status'
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!