Untitled
unknown
mysql
a year ago
2.0 kB
5
Indexable
SELECT releasedate, date(orderdate) orderdate, (SELECT CASE WHEN COUNT(*) = 1 THEN 'single' WHEN COUNT(*) = 2 THEN '2-item' WHEN COUNT(*) = 3 THEN '3-item' WHEN COUNT(*) > 3 THEN 'multi' END FROM shipment WHERE invoice = t2.invoice AND status IN ('shipped', 'printing' , 'picked', 'printed', 'picksorted') AND productcode != giftproductcode GROUP BY invoice) AS 'order type', invoice, case when giftproductcode = 'GWRA-UA8' then 'Wrap UA8' when giftproductcode = 'GWRA-UA1' then 'Giftwrap' else 'Gift Certificate' end as 'gift type', REPLACE(serialnumber, 's', '') AS serial, productcode, option1, status, (SELECT shippingOption FROM orders WHERE transactionid = t2.transactionid LIMIT 1) as shippingOption, IF(serialnumber LIKE 's%', (SELECT IF(bin != '', CONCAT(location, ' (', bin, ')'), location) FROM inventoryitem FORCE INDEX (SERIALNUMBER) WHERE serial = serialnumber), '') AS current_location FROM (SELECT printbatch AS releasedate, invoice AS invoicenum, productcode as giftproductcode FROM shipment WHERE (productcode in ('GWRA-UA1','GWRA-UA8') OR productcode LIKE 'GIFT-%') AND status IN ('shipped', 'printing' , 'picked', 'printed', 'picksorted') AND ordertype != 20) t1 JOIN shipment t2 ON t1.invoicenum = t2.invoice WHERE status IN ('shipped', 'printing' , 'picked', 'printed', 'picksorted') AND productcode != giftproductcode AND orderdate >= '2024-04-13 00:00:00' ORDER BY invoice
Editor is loading...
Leave a Comment