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;

This query will filter out records with the Shipped value

SQL script to Filter out records with values you don't want

SQL