This was discussed at a List Manager session at the conference, but with the likelihood of List Builder pulling household records an issue arises with deceased constituents: to whit households are not generally marked as deceased: certainly not when only one affiliate is deceased, but not even if two are. We toyed with having a process to mark the households themselves, but consensus in the room was just to create a more clever suppression list.
I failed to figure out a good way of building this list, however: I could, certainly, look for deceased constituents and replace with household, but that would catch households where only, say, the A2 name was deceased, and we don't want to suppress those. So I wrote a manual query part to my original individual-only list:
select distinct aff.group_customer_no as customer_nofrom VS_AFFILIATION as aff inner join T_CUSTOMER as c on c.customer_no = aff.individual_customer_nowhere aff.primary_ind <> 'N' and aff.name_ind = -1 and c.name_status = 2 -- deceased
union
Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN T_CUSTOMER e (Nolock) ON e.customer_no = a.customer_noWhere IsNull(a.inactive, 1) = 1 AND ISNULL(e.name_status, 1) in (2)
So, this will list any deceased individual, and also any household where the A1 is deceased. It will not list a household where other members are deceased but the A1 is alive, but it will list a household where the A1 is deceased yet others (even the A2) are alive. I'm sorting this out with my Development department currently, but that seems like a good solution to me: either the A1/A2 have yet to be flipped, and any such constituent pulled would run the risk of an outdated salutation, or the record is being kept that way intentionally because while the A1 had a business relationship with our organization, it is recognized that the A2 had no such interest.
Couple of questions. First, am I missing anything not joining to V_CUSTOMER_WITH_PRIMARY_GROUP? I'm not familiar with how that's being applied to list queries. Second, any obvious mistakes? My spot checks look good, the numbers seem right.
--Gawain
Hi Gawain,
This statement is a view we use to get rid of deceased customers and households with no living affiliates. It works fine for us. It will not treat cases with a living a2/deceased a1 as deceased. Note that it references V_CUSTOMER_WITH_HOUSEHOLD instead of V_CUSTOMER_WITH_PRIMARY_AFFILIATES for the purposes of seeing a household as "deceased" and also that this view does not consider inactive status at all.
Matt
create view [dbo].[lv_deceased_customers] as
--households with no living affiliates.select distinct customer_nofrom dbo.T_CUSTOMER c join dbo.TR_CUST_TYPE g on c.cust_type = g.idwhere g.cust_group = 3 --hh and customer_no not in (select h.customer_no from V_CUSTOMER_WITH_HOUSEHOLD h join dbo.T_CUSTOMER c on h.expanded_customer_no = c.customer_no join dbo.TR_CUST_TYPE ct on c.cust_type = ct.id where ct.cust_group <>3 and coalesce(name_status,1) = 1) union--any other deceased entityselect customer_no from dbo.T_CUSTOMER c join dbo.TR_CUST_TYPE g on c.cust_type = g.idwhere g.cust_group <> 3 --ind and coalesce(name_status,1) > 1
Thanks for the script, I'll look into that.