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;