| SQL |
SELECT distinct (b.attr2) as origAttr2, max(CASE WHEN b.attr2_alias IS NULL OR RTRIM(LTRIM(b.attr2_alias)) = '' THEN b.attr2 ELSE b.attr2_alias END) as attr2, max(a.sku_id) as sku_id, max(b.attr2_entry_id) as attr2_entry_id, sum(e.qoh+e.qc) as qoh
FROM tb_skus a
LEFT JOIN tb_attr2_entries b WITH (NOLOCK) ON (b.attr2_entry_id = a.attr2_entry_id)
LEFT JOIN tb_sku_buckets e WITH (NOLOCK) ON (e.sku_id = a.sku_id)
WHERE
(a.style_id IN (
SELECT style_id
FROM tb_styles WITH (NOLOCK)
WHERE web_style = 'BWUS-01' and web_style > '' and web_style is not null
) or a.style_id =?)
AND isNull(a.sell_web,'Y') = 'Y'
AND e.store_id in(98,8,9,2,5,7,6,3,4)
--AND e.qoh > 0
GROUP BY b.attr2
HAVING sum(e.qoh+e.qc) > 0 |
