Untitled

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 Warehouse
FROM
    inventoryitem
WHERE
    location IN ('7-B20D-3c',
'7-D32W-1c',
'7-B35C-2c',
'7-B42B-4a',
'7-LA23G-3g',
'7-C6I-5b',
'7-D19T-4a',
'7-LA25A-1b',
'7-UC16T-3c',
'7-D14W-3b',
'7-C22K-5a',
'7-C54H-2b',
'7-UC13K-4c',
'7-A37C-4a',
'7-A51A-0c',
'7-D50B-1b',
'7-C6W-3c',
'7-B62A-2b',
'7-VB12H-3a',
'7-B28B-1b',
'7-D60A-1c',
'7-LA11B-4g',
'7-UC25E-1a',
'7-ULB4B-3a',
'7-D18N-7c',
'7-B36C-0b',
'7-B36I-2b',
'7-ULB15D-4d',
'7-C9W-2a',
'7-ULB29A-1c',
'7-B32D-5a',
'7-ULA24A-1c',
'7-D41O-7c',
'7-B6J-3b',
'7-C62D-4c',
'7-UC8Z-4a',
'7-D18S-3b',
'7-VA21F-4a',
'7-UD48H-1a',
'7-UA57G-4b',
'7-UB44J-1c',
'7-UD37U-2a',
'7-ULA9D-1g')
        AND qtyonhand = 1
        GROUP BY location
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-B68A-3b' , '7-UD32L-1a',
        '7-UC24H-3b',
        '7-C50S-2c',
        '7-VC47M-1a',
        '7-C1U-2c',
        '7-VA28H-0a',
        '7-ULA4E-1e',
        '7-VC52B-2c',
        '7-UC65I-1b',
        '7-UA27E-5a',
        '7-UC58H-2b',
        '7-UD18J-3b',
        '7-VC33N-4a',
        '7-ULA10C-4a',
        '7-C61S-2b',
        '7-VC21T-2b',
        '7-UD54A-1c',
        '7-301C-1d',
        '7-VC7Y-2b',
        '7-B67K-4a',
        '7-D24X-2a',
        '7-A49C-1b',
        '7-D36B-6b',
        '7-VA45E-3b',
        '7-VB13G-3a',
        '7-VA22D-1a',
        '7-B3J-1a',
        '7-B12K-0a',
        '7-LB4D-5c',
        '7-C19M-3b',
        '7-C32R-0b',
        '7-D2X-4a',
        '7-UC40N-4c',
        '7-VC11M-1b',
        '7-E30H-2a',
        '7-UD31Q-4b',
        '7-C37Q-5b',
        '7-UC66I-1b',
        '7-VB26H-4a',
        '7-VC69J-4a',
        '7-VC68W-2c',
        '7-LB3C-1e',
        '7-UB44F-4a',
        '7-C66U-3b',
        '7-B17E-2a',
        '7-UB63G-3c',
        '7-LB27B-1g',
        '7-UA53H-4b',
        '7-UC19S-2a',
        '7-UA56E-3b',
        '7-VC59K-2c',
        '7-UD11J-2b',
        '7-UC18P-4b',
        '7-VC22I-4a',
        '7-B38L-5a',
        '7-D48Q-1a')
        AND qtyonhand = 1
        group by location, scandate
ORDER BY location, bin ASC