Untitled

avatar
unknown
sqlserver
3 years ago
2.2 kB
11
Indexable
SELECT 
    serialnumber,
    bin,
    location,
    DATE(lastscandate) scandate,
    CASE
        WHEN
            (location LIKE '7-L%'
                OR location LIKE '7-UL%')
        THEN
            'La Palma'
        ELSE 'Moore'
    END AS Warehouse
FROM
    inventoryitem
WHERE
    location IN ('7-B24G-3c',
'7-UB2C-2c',
'7-UD5L-1c',
'7-VB17N-2a',
'7-UD13A-2b',
'7-LA22C-5d',
'7-C35H-2c',
'7-E25P-5c',
'7-ULB11D-1g',
'7-C61W-3a',
'7-D55G-1b',
'7-LA7F-3e',
'7-C18B-0a',
'7-UC11L-4a',
'7-E23P-7a',
'7-C2W-3a',
'7-VC3Q-1b',
'7-VC61M-4b',
'7-UC30Y-1c',
'7-D47M-2c',
'7-B3H-3b',
'7-E25H-2c',
'7-202D-2a',
'7-E14O-2a',
'7-LB6A-3e',
'7-UB53D-5c',
'7-UC19K-1a',
'7-VC33Q-4a',
'7-B23B-1a',
'7-C11L-1a',
'7-A31C-1b',
'7-UD27L-1a',
'7-UC63W-1a',
'7-VC24M-3c',
'7-C2E-2a',
'7-UC3U-4b',
'7-D48T-4c',
'7-D39R-3b',
'7-UC1A-2c',
'7-VC50P-2a',
'7-LA14F-5b',
'7-VC44V-4a',
'7-E37A-3d',
'7-LA26C-5a',
'7-VB59K-1a',
'7-C35Y-3b',
'7-LA28C-1e')
        AND qtyonhand = 1
ORDER BY location ASC;

SELECT 
    count(serialnumber) serials,
    bin,
    location,
    DATE(lastscandate) scandate,
    CASE
        WHEN
            (location LIKE '7-L%'
                OR location LIKE '7-UL%')
        THEN
            'La Palma'
        ELSE 'Moore'
        END AS 'WH',
        CASE WHEN date(lastscandate) >= '2022-11-18' THEN 'ConfirmedInShelf'
        WHEN date(lastscandate) < '2022-11-18' THEN 'ConfirmedMissing'
    END AS 'InShelf'
FROM
    inventoryitem
WHERE
    location IN ('7-B24G-3c',
'7-UB2C-2c',
'7-UD5L-1c',
'7-VB17N-2a',
'7-UD13A-2b',
'7-LA22C-5d',
'7-C35H-2c',
'7-E25P-5c',
'7-ULB11D-1g',
'7-C61W-3a',
'7-D55G-1b',
'7-LA7F-3e',
'7-C18B-0a',
'7-UC11L-4a',
'7-E23P-7a',
'7-C2W-3a',
'7-VC3Q-1b',
'7-VC61M-4b',
'7-UC30Y-1c',
'7-D47M-2c',
'7-B3H-3b',
'7-E25H-2c',
'7-202D-2a',
'7-E14O-2a',
'7-LB6A-3e',
'7-UB53D-5c',
'7-UC19K-1a',
'7-VC33Q-4a',
'7-B23B-1a',
'7-C11L-1a',
'7-A31C-1b',
'7-UD27L-1a',
'7-UC63W-1a',
'7-VC24M-3c',
'7-C2E-2a',
'7-UC3U-4b',
'7-D48T-4c',
'7-D39R-3b',
'7-UC1A-2c',
'7-VC50P-2a',
'7-LA14F-5b',
'7-VC44V-4a',
'7-E37A-3d',
'7-LA26C-5a',
'7-VB59K-1a',
'7-C35Y-3b',
'7-LA28C-1e')
        AND qtyonhand = 1
        group by location, bin, scandate
ORDER BY location, bin ASC
Editor is loading...