-- All products currently in user by a customer
select c.customer_id, pd.longdescription, p.code, p.legacyproductcode,
case when sd.code = '*' then null
else sd.code end as size_code
from product p
inner join product_desc pd on pd.product_id = p.product_id
inner join productlistpercustomer plpc on plpc.product_id = p.product_id
inner join customer c on c.customer_id = plpc.customer_id
inner join sizedefinition sd on sd.sizedefinition_id = plpc.sizedefinition_id
where c.customernumber = '2699810'
and pd.language_id = 1

-- what products is this wearer using
select w.fullname, pd.longdescription, p.code, sd.code from wearerinventory wi
inner join weareremployment we on we.weareremployment_id = wi.weareremployment_id
inner join wearer w on w.wearer_id = we.wearer_id
inner join product p on p.product_id = wi.product_id
inner join product_desc pd on pd.product_id = wi.product_id
inner join sizedefinition sd on sd.sizedefinition_id = wi.sizedefinition_id
where wi.weareremployment_id = 164740
and pd.language_id = 1

-- what products are available for customer 5672569
select c.customernumber, con.code as contract_code, p.code, pd.longdescription, sd.code as size_code from customer c
inner join contract con on con.code =
case when c.nationalaccount_id is null then 'SERVICE EXPRESS'
else (select code from nationalaccount where nationalaccount_id = c.nationalaccount_id) end
inner join contractproduct conp on conp.contract_id = con.contract_id
inner join product p on conp.product_id = p.product_id
inner join product_desc pd on pd.product_id = p.product_id
inner join sizedefinition sd on sd.sizedefinition_id = conp.sizedefinition_id
where customernumber = 2699810--5672569
and pd.language_id = 1