78 lines
2.1 KiB
SQL
78 lines
2.1 KiB
SQL
WITH
|
|
target AS (select 'replace_new_mold' new_mold)
|
|
,basemix AS (
|
|
SELECT
|
|
part,
|
|
stlc,
|
|
colc,
|
|
colgrp,
|
|
coltier,
|
|
sizc,
|
|
SUM(value_usd) value_usd
|
|
FROM
|
|
rlarp.osm_pool o
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.item = o.part
|
|
WHERE
|
|
-----------------scenario----------------------------
|
|
where_clause
|
|
-----------------additional params-------------------
|
|
AND calc_status||flag <> 'CLOSEDREMAINDER' --exclude short ships when building order adjustments
|
|
AND order_date <= ship_date
|
|
GROUP BY
|
|
part,
|
|
stlc,
|
|
colc,
|
|
colgrp,
|
|
coltier,
|
|
sizc
|
|
)
|
|
--SELECT * FROM basemix
|
|
,tag AS (
|
|
SELECT
|
|
b.part
|
|
,b.value_usd
|
|
--,b.stlc
|
|
--,b.sizc
|
|
--,b.colgrp
|
|
--,b.coltier
|
|
--,b.colc
|
|
,i.item
|
|
,CASE WHEN b.sizc = i.sizc THEN '1' ELSE '0' END||
|
|
CASE WHEN i.aplnt = 'I' THEN '0' ELSE '1' END||
|
|
CASE WHEN b.colgrp = i.colgrp THEN '1' ELSE '0' END||
|
|
CASE WHEN b.coltier = i.coltier THEN '1' ELSE '0' END||
|
|
CASE WHEN b.colc = i.colc THEN '1' ELSE '0' END||
|
|
CASE WHEN substring(b.part,9,12) = substring(i.item,9,12) THEN '1' ELSE 0 END fit
|
|
FROM
|
|
basemix b
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.stlc = (SELECT new_mold FROM target WHERE new_mold <> '')
|
|
AND CASE WHEN b.sizc = i.sizc THEN '1' ELSE '0' END||
|
|
CASE WHEN i.aplnt = 'I' THEN '0' ELSE '1' END||
|
|
CASE WHEN b.colgrp = i.colgrp THEN '1' ELSE '0' END||
|
|
CASE WHEN b.coltier = i.coltier THEN '1' ELSE '0' END||
|
|
CASE WHEN b.colc = i.colc THEN '1' ELSE '0' END||
|
|
CASE WHEN substring(b.part,9,12) = substring(i.item,9,12) THEN '1' ELSE 0 END >= '111110'
|
|
)
|
|
--SELECT * FROM tag
|
|
,rsort AS (
|
|
SELECT
|
|
part,
|
|
value_usd,
|
|
item swap,
|
|
fit,
|
|
ROW_NUMBER() OVER (PARTITION BY tag.part ORDER BY fit DESC) ranked
|
|
FROM
|
|
tag
|
|
ORDER BY
|
|
part
|
|
,fit desc
|
|
)
|
|
SELECT
|
|
json_agg(row_to_json(rsort)) x
|
|
FROM
|
|
rsort
|
|
WHERE
|
|
ranked = 1
|