| SQL |
SELECT distinct
(b.siz) as origSiz,
max(CASE WHEN b.siz_alias IS NULL OR RTRIM(LTRIM(b.siz_alias)) = '' THEN b.siz ELSE b.siz_alias END) as siz,
max(b.size_order) as size_order,
max(a.sku_id) as sku_id,
max(a.scale_entry_id) as scale_entry_id,
sum(e.qoh+e.qc) as qoh,
max(a.style_id) as style_id
FROM tb_skus a WITH (NOLOCK)
LEFT JOIN tb_size_entries b WITH (NOLOCK)
on (b.scale_entry_id = a.scale_entry_id)
LEFT JOIN tb_sku_buckets e WITH (NOLOCK)
on (e.sku_id = a.sku_id)
LEFT JOIN tb_attr1_entries d WITH (NOLOCK)
on (d.attr1_entry_id = a.attr1_entry_id)
LEFT JOIN
(
SELECT style_id
FROM tb_styles WITH (NOLOCK)
WHERE web_style = ?
and web_style is not null
) sq
ON(a.style_id = sq.style_id)
WHERE (sq.style_id is not null OR a.style_id = ?)
AND (b.siz_alias is null OR b.siz_alias NOT IN ('NO SHOW','NOSHOW') )
AND isNull(a.sell_web,'Y') = 'Y'
AND e.store_id in (98,8,9,2,5,7,6,3)
GROUP BY b.siz
HAVING sum(e.qoh+e.qc) > 0
ORDER BY max(b.size_order), max(a.scale_entry_id) |
