forecast_api/route_sql/swap_fit.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