Revision: 65854
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at January 30, 2014 02:31 by heathbo
Initial Code
SELECT
v.VIAL_ID,
v.ORDER_ID,
(
SELECT COUNT(v2.vial_id)
FROM mgph.ord_vials v2
WHERE v2.order_id = v.order_id
) As NumberOfVials,
s.station,
s.TIME_STAMP
FROM
mgph.ORD_VIALS v
JOIN mgph.vial_sort_events s
ON s.vial_id = v.vial_id
AND s.order_id = v.order_id
AND s.TIME_STAMP = (SELECT MAX(s2.TIME_STAMP)
FROM mgph.vial_sort_events s2
WHERE s2.vial_id = v.vial_id
AND s2.order_id = v.order_id)
JOIN mgph.ord_order_status os
ON os.order_id = s.order_id
WHERE
s.station IN('BOTTLE TABLE01')
AND s.time_stamp <=(current_date - 8/24)
AND s.ORDER_ID NOT IN (SELECT order_id from mgph.ord_order_status where status_id = (SELECT sc.id FROM mgph.STATUS_CODES sc WHERE sc.description = 'Shipped'))
GROUP BY v.VIAL_ID, v.ORDER_ID ,v.VERIFICATION_STATUS, v.Pallet_no, s.station, s.TIME_STAMP;
Initial URL
Initial Description
This query will filter out records with the Shipped value
Initial Title
SQL script to Filter out records with values you don't want
Initial Tags
Initial Language
SQL