CODEWITHSUNDEEP
sqlsql-server-2012
Matías W.
Matías W.

Reputation: 350

find rejected items from the customer's order

I need to generate a report of the rejected items of an order, I have to do it when the order has finished being processed by de system and the conditions that I have to consider that the order has stopped being processed are:

  1. The status of the order in the process is equal to or greater than 600
  2. All items in the order were rejected and are in 999 status

I want to make an SQL query that considers the two previous conditions to bring me the rejecteds items from the order when it is no longer processed by the system.

scenario example:

enter image description here

so, I am trying them in the following way

select * from order_detail_status 
where  order_number = 'OR_001'
and  process_status= '999'
and process_id = (select max(process_id) from  configuracion.order_detail_status where  order_number = 'OR_001' and process_status >= 600)

this would work if only scenario 1 existed, but for scenario 2 the request never reaches that status, so I am trying to add a second condition:

or (select distinct (process_status) from configuracion.order_detail_status where order_number = 'OR_002' ) = '999'

in the second condition I want to indicate that all the records of the order were rejected with the state 999, but it does not work for me, any suggestions?

Upvotes: 0

Views: 397

Answers (1)

Bob McCormick
Bob McCormick

Reputation: 225

If you want to find orders where ALL items have process_status of 999, then try something like this:

SELECT order_number, MIN(process_status) AS minps, MAX(process_status) AS maxps
FROM order_detail_status 
GROUP BY order_number
HAVING minps=maxps AND minps=999

Grouping the lines by order and then doing min() and max() gives you the highest and lowest status for the order. If they match, then there is only one status for all items in the order. If the single status is 999 (or > 600), then you have the answer.

HAVING is like a WHERE condition but operates after the grouping is done.

Results:

OR_002  999 999

Upvotes: 1

Related Questions