Advanced Search

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

/ Published in: SQL
Save to your folder(s)
This query will filter out records with the Shipped value

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT 
  2. 	v.VIAL_ID, 
  3. 	v.ORDER_ID, 
  4. 	(
  5. 	  SELECT COUNT(v2.vial_id)
  6. 	  FROM mgph.ord_vials v2
  7. 	  WHERE v2.order_id = v.order_id
  8. 	) AS NumberOfVials,
  9. 	s.station,
  10. 	s.TIME_STAMP
  11. 	FROM
  12. 	mgph.ORD_VIALS v
  13.  
  14. 	JOIN mgph.vial_sort_events s
  15. 	ON s.vial_id = v.vial_id
  16. 	AND s.order_id = v.order_id
  17. 	AND s.TIME_STAMP = (SELECT MAX(s2.TIME_STAMP) 
  18.         FROM mgph.vial_sort_events s2 
  19.         WHERE s2.vial_id = v.vial_id 
  20.         AND s2.order_id = v.order_id)
  21.  
  22.         JOIN mgph.ord_order_status os
  23.         ON os.order_id = s.order_id
  24.  
  25. 	WHERE 
  26. 	s.station IN('BOTTLE TABLE01')
  27.         AND s.time_stamp <=(CURRENT_DATE - 8/24) 
  28.         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'))
  29.  
  30.         GROUP BY v.VIAL_ID, v.ORDER_ID ,v.VERIFICATION_STATUS, v.Pallet_no, s.station, s.TIME_STAMP;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.