Help requested-list criteria for current addresses

Both sides of your union statement will exclude people that don't have a Email Address (vs_eaddress). You might want to use

"LEFT JOIN vs eaddress" instead of "JOIN vs_eaddress"

Also you are joining t_address to vs_address which is not getting you anything as they are basically the same table (vs_address is a view). You should remove the JOIN to vs_address from the upper query.

Also you may have address start dates set to NULL in your t_ address table which will be excluded in your first statement by e.start_dt >= '2012/05/08'. I'm guessing you were trying to pull these in the second statement but you still have full Join to vs_eaddress which means that the constituent even if has an address without a start date (amongst your other conditions) would still have to an eaddress record that meets your eaddress conditions due to the full "JOIN vs_eaddress". 

-doug

 

Both sides of your union statement will exclude people that don't have a Email Address (vs_eaddress). You might want to use

"LEFT JOIN vs eaddress" instead of "JOIN vs_eaddress"

Also you are joining t_address to vs_address which is not getting you anything as they are basically the same table (vs_address is a view). You should remove the JOIN to vs_address from the upper query.

Also you may have address start dates set to NULL in your t_ address table which will be excluded in your first statement by e.start_dt >= '2012/05/08'. I'm guessing you were trying to pull these in the second statement but you still have full Join to vs_eaddress which means that the constituent even if has an address without a start date (amongst your other conditions) would still have to an eaddress record that meets your eaddress conditions due to the full "JOIN vs_eaddress". 

-doug

 

Hi,

I am trying to create a list of accounts who are locally in residence according to address dates (or where no address date is indicated) and have not bought tickets to specific show.  The query below omits some current residents and I'm not sure what I missed.  Can someone help?  Thanks so much

Select Distinct a.customer_no
 From t_customer a (NOLOCK)
JOIN t_address e (NOLOCK) ON a.customer_no = e.customer_no
JOIN vs_address f (NOLOCK) ON a.customer_no = f.customer_no
JOIN vs_eaddress g (NOLOCK) ON a.customer_no = g.customer_no
 Where  IsNull(a.inactive, 1) = 1
 AND ISNULL(a.emarket_ind,3) not in  (2)
 AND e.start_dt >=  '2012/05/08'
 AND f.state in  ('FL')
 AND ISNULL(g.market_ind, 'N') not in  ('N') 
 AND ISNULL(g.inactive, 'N') not in  ('Y') and not exists (select 1 from lvs_tkt_hist (NOLOCK) where a.customer_no = lvs_tkt_hist.customer_no and lvs_tkt_hist.perf_no IN  (7890,8080)) 
UNION
Select Distinct a.customer_no
 From t_customer a (NOLOCK)
JOIN vs_address e (NOLOCK) ON a.customer_no = e.customer_no
JOIN vs_eaddress f (NOLOCK) ON a.customer_no = f.customer_no
 Where  IsNull(a.inactive, 1) = 1
 AND ISNULL(a.emarket_ind,3) not in  (2)
 AND e.state in  ('FL')
 AND ISNULL(f.market_ind, 'N') not in  ('N') 
 AND ISNULL(f.inactive, 'N') not in  ('Y') and not exists (select 1 from lvs_tkt_hist (NOLOCK) where a.customer_no = lvs_tkt_hist.customer_no and lvs_tkt_hist.perf_no IN  (7890,8080))

 

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!