Finding expired constituencies in a list

Hi Max,

I created a view in our impresario database upon which I could create List Manager criteria.  It's a slight reworking of the Tessi supplied view but allows for expired constituencies to be considered when building a list.  I created 3 criteria:

1. Constituency (all) - to distinguish it from the canned criterion
2. Constituency Start Date
3. Constituency End Date

I'd be happy to share - email me directly at martin.keen@nida.edu.au if you would like more..........

Martin

Here's the SQL I use in List Manager. Just change the constituency number (in my example 1) and constituency end date range (or delete the end date range all together).

Select Distinct c.customer_no
From T_CUSTOMER AS c
JOIN (SELECT const.customer_no FROM TX_CONST_CUST AS const WHERE const.constituency in (1) AND const.end_dt BETWEEN '2018/07/31' AND '2018/08/01') AS e ON e.customer_no = c.customer_no
Where IsNull(c.inactive, 1) = 1

Unknown said:

Curious if someone out there has an easy solution for creating a list of records where a desired constituency is inactive.

Hi, Max. Tess provides a View that makes this easy:

select a.customer_no
from TX_CONST_CUST a
where not exists (
select 1
from VX_CONST_CUST_ACTIVE b
where a.customer_no = b.customer_no
and a.constituency = b.constituency
)
and a.constituency = 1 -- substitute desired const ID

Could be used in List Manager or elsewhere. 

Thanks for the responses everyone! Super helpful.