did you try something as
declare @region_assoc_prod int
set @region_assoc_prod = (select count(sa_status) from products)
select product, region, status
from products prd
inner join pd_status S on s.product_status_key=prd.status_key
where prd..product_status in ('active', 'on...