• Replies 2 replies
  • Subscribers 336 subscribers
  • Views 46 views
  • Users 0 members are here
Related

Help requested-list criteria for current addresses

Former Member
Former Member $organization

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))

 

Parents
  • $organization

    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

     

Reply
  • $organization

    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

     

Children
No Data