Untitled

mail@pastecode.io avatar
unknown
sql
a year ago
2.2 kB
4
Indexable
Never
SELECT 
    COUNT(j2.serialnumber) serials,
    j1.yr,
    j1.mo,
    CONCAT('$', FORMAT(ROUND(SUM(cost)), 'c')) cost
FROM
    (SELECT 
        YEAR(updatedate) yr,
            MONTH(updatedate) mo,
            t1.productcode,
            t1.option1,
            serialnumber,
            source
    FROM
        inventoryupdate t1
    JOIN inventoryupdate_extend t2 ON t1.id = t2.inventoryupdateid
    WHERE
        reasoncode = '54'
            AND quantity < prevupdatequantity
            AND DATE(updatedate) >= '2022-01-01'
            AND DATE(updatedate) <= '2023-12-31'
    LIMIT 100000000) j1
        LEFT JOIN
    inventoryitem j2 ON j1.serialnumber = j2.serialnumber
       AND (location = 'returns'
        OR location = 'Repack'
        OR location = 'FWD Returns'
        OR location = 'Repackaging Restore'
        OR location = 'Repackaging Defective'
        OR location = 'cant find:pending'
        OR location = 'Stocking Pending'
        OR location = 'Consolidation Discrepancy'
        OR location = 'Pending CC'
        OR location = 'StagnantPP'
        OR location = 'repackaging restore'
        OR location = 'PA Returns'
        OR location = 'Repack'
        OR location = 'PA RT in-trnst'
        OR location = 'Model Size'
        OR location = 'receiving'
        OR location = 'Receiving Defective'
        OR location = 'SA Pending'
        OR location = 'CS Defect Pending'
        OR location = 'CS Returns'
        OR location = 'defective: inspect'
        OR location = 'defective: restore'
        OR location = 'defective: dry cleaner'
        OR location = 'defective: dry cleaner '
        OR location = 'QA Dry Clean'
        OR location = 'defective: at dry cleaners'
        OR location = 'QA PENDING'
        OR location = 'QC Pending'
        OR location LIKE '7-R%'
        OR location LIKE '7-S%'
        OR location LIKE '7-R%'
        OR location LIKE '7-S%'
        OR location LIKE '7-T%'
        OR location LIKE '7-800%'
        OR location LIKE '7-Q')
        AND qtyonhand = 0
        JOIN
    product p ON p.code = j2.product
GROUP BY yr , mo
ORDER BY mo ASC
LIMIT 10000000000