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) 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)
INNER JOIN (
SELECT DISTINCT sku_id
, sum(qoh+qc) as qoh
FROM dbo.tb_sku_buckets WITH (NOLOCK)
WHERE store_id in(98,8,9,2,5,7,6,3)
GROUP BY sku_id
) e on (a.sku_id = e.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.qoh > 1
GROUP BY b.siz
ORDER BY max(b.size_order), max(a.scale_entry_id)

SQLPARAMETERS

array

1

32550

2

134015

query

RESULTSET

query

ATTR1

ATTR1_ALIAS

ATTR1_ENTRY_ID

FAM

HEX

ORIGATTR1

QOH

SIZE

SKU_ID

SORT_ORDER

STYLE_ID

1

BLK

[empty string]

136743

[empty string]

[empty string]

BLK

39

151352

519014

[empty string]

134015

2

KTPG

[empty string]

136744

[empty string]

[empty string]

KTPG

35

151352

519019

[empty string]

134015

3

Forge Grey

Forge Grey

136739

[empty string]

[empty string]

FGE

29

151352

499243

[empty string]

134015

4

WDCE

[empty string]

136741

[empty string]

[empty string]

WDCE

8

151352

499245

[empty string]

134015

5

PAGH

[empty string]

136742

[empty string]

[empty string]

PAGH

5

151352

499246

[empty string]

134015

6

TRNA

[empty string]

136740

[empty string]

[empty string]

TRNA

5

151352

499244

[empty string]

134015

CACHED

false

EXECUTIONTIME

2

SQL

SELECT distinct (b.attr1) as origAttr1, max(b.attr1_alias) as attr1_alias, max(CASE WHEN b.attr1_alias IS NULL OR RTRIM(LTRIM(b.attr1_alias)) = '' THEN b.attr1 ELSE b.attr1_alias END) as attr1, max(a.sku_id) as sku_id, max(b.attr1_entry_id) as attr1_entry_id, sum(e.qoh) as qoh, max(b.attr1_order) as sort_order, max(c.attr1_hex) as hex,max(d.attr1_fam) as fam, max(a.style_id) as style_id, max(a.SCALE_ENTRY_ID) as size
FROM tb_skus a
LEFT JOIN tb_attr1_entries b WITH (NOLOCK) ON (b.attr1_entry_id = a.attr1_entry_id)
INNER JOIN (
SELECT DISTINCT sku_id
, sum(qoh+qc) as qoh
FROM dbo.tb_sku_buckets WITH (NOLOCK)
WHERE store_id in(98,8,9,2,5,7,6,3)
GROUP BY sku_id
) e on (a.sku_id = e.sku_id)
LEFT JOIN tb_attr1_hexes c WITH (NOLOCK) ON (b.attr1=c.attr1)
LEFT JOIN tb_size_entries k WITH (NOLOCK) on (k.scale_entry_id = a.scale_entry_id)
LEFT JOIN tb_attr1_families d WITH (NOLOCK) ON (c.attr1_fam_id = d.attr1_fam_id)
WHERE
a.style_id =? AND (b.attr1_alias is null OR b.attr1_alias NOT IN ('NO SHOW','NOSHOW') )
AND e.qoh > 1
AND isNull(a.sell_web,'Y') = 'Y'
GROUP BY b.attr1
ORDER BY sum(e.qoh) DESC --max(b.attr1_order), max(b.attr1_entry_id)

Patagonia makes these everyday Patagonia Men's Essential Boxers - 6in for every type of day—because sometimes you’re desperately running through O’Hare and other times you’re putting your feet up and opening a cold one. Light on the skin and endlessly breathable, these Essential Boxers provide all-day comfort with a fabric blend of TENCEL® lyocell and spandex stretch jersey that feels supersoft and natural yet has all the technical benefits to wick moisture and stretch. At the waist, an anti-roll, comfort waistband stays put without pinching, binding or chafing. Patagonia designed their Essentials for a bulk-free fit with ergonomic patterning to keep them from riding up or bunching. Details include flatlock, chafe-free seaming throughout for greater comfort and a fully functioning fly. Essential Boxers are also Fair Trade Certified sewn, which means the people who made them earned a premium for their labor.

product specs

Made from moisture-wicking, breathable TENCEL® lyocell/spandex stretch jersey with a supersoft hand for all-day comfort