We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
but need this:
NewStatus Total Received 47 In Progress 87 Shipped 464 Order Finalised 134598 Deleted Order 907
Here is the query:
NewStatus Total Received 47 In Progress 87 Ready For Despatch 0 Shipped 464 Partial Shipped 0 Order Finalised 134598 Deleted Order 907
SELECT NewStatus, COUNT(*) as Total FROM (SELECT CASE WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress' WHEN order_status = 'G' AND completed = 'N' THEN 'Received' WHEN order_status = 'C' THEN 'Ready For Despatch' WHEN order_status = 'S' THEN 'Shipped' WHEN order_status = 'P' THEN 'Partial Shipped' WHEN order_status = 'D' THEN 'Deleted Order' WHEN order_status = 'I' THEN 'Order Finalised' WHEN order_status = 'R' THEN 'Obsolete' ELSE 'Unknown' END AS NewStatus FROM ORDER_HEADER INNER JOIN ( SELECT order_id , completed , completed_on , row_number() over (partition BY order_id ORDER BY completed_on DESC, completed) AS row_ref FROM ORDER_PROC_SEQUENCE ) as PROC_SEQUENCE ON PROC_SEQUENCE.order_id = ORDER_HEADER.order_id AND PROC_SEQUENCE.row_ref = 1 WHERE (customer_id like '%') GROUP BY ORDER_HEADER.order_id, ORDER_HEADER.order_status, PROC_SEQUENCE.completed) s WHERE (NewStatus != 'Unknown' or NewStatus != 'Obsolete') GROUP BY s.NewStatus ORDER BY (CASE s.NewStatus WHEN 'Received' THEN 1 WHEN 'In Progress' THEN 2 WHEN 'Ready For Despatch' THEN 3 WHEN 'Partial Shipped' THEN 4 WHEN 'Shipped' THEN 5 WHEN 'Order Finalised' THEN 6 WHEN 'Deleted Order' THEN 7 ELSE 100 END) ASC
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.