Untitled
unknown
mysql
2 years ago
1.7 kB
4
Indexable
Never
select cutoff,invoicetype,shippingoption,M.productcode, M.option1,M.serialnumber,pullorleave,L.invoice, tote from (SELECT T.cutoff_time AS CutOff, CASE WHEN ordercount = 1 THEN 'single' WHEN ordercount = 2 THEN 'two_item' WHEN ordercount >= 3 THEN 'multi' END AS InvoiceType, K.* FROM (SELECT O.shippingoption, CONCAT('s', GROUP_CONCAT(DISTINCT I.serialnumber)) AS Serials, CASE WHEN O.shippingoption IN ('3-6 Day Global Express' , 'Employee Shipping', 'Will Call', '2nd Day Air', 'UPS Worldwide Saver', 'Standard RRD', 'Fedex Ground') THEN 'LEAVE' WHEN O.shippingoption IN ('Easy Express' , 'Easy Express DDP', 'Fedex International Priority', 'Next Day Air', 'Priority Mail International', 'Fedex Same Day', 'Next Day Air Saver', 'DHL Mail') THEN 'PULL' ELSE NULL END AS PullOrLeave, S.invoice, COUNT(S.invoice) AS ordercount, I.bin AS Tote FROM (SELECT invoice, status, REPLACE(serialnumber, 's', '') serialnumber, productcode, option1 FROM shipment WHERE status IN ('Picked' , 'Printing', 'Printed') AND ordertype = 20) S JOIN orders O ON O.invoicenum = S.invoice JOIN inventoryitem I ON I.serialnumber = S.serialnumber AND bin LIKE 'T%' AND bin != '' AND bin NOT LIKE 'Tp%' GROUP BY S.invoice ORDER BY shippingoption , bin DESC) K JOIN id.shipping_pickuptime T ON T.shippingoption = K.shippingoption) L join shipment M on L.invoice= M.invoice and printbatch >= current_date group by serialnumber