Posted By

heathbo on 01/30/14


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Beppoi


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


 / Published in: SQL
 

This query will filter out records with the Shipped value

  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  

You need to login to post a comment.