GraemeBerry
Technical User
I have a table containing details of order_qty, despatch_qty and invoice_qty, we call this table salesorders.
I need to return an 'order status' field narrative based upon nested if and statements but cannot get this to work in SQL - I belive the CASE funtion should do the trick but have had no luck.
In short the narrative to be returned would be;
If order_qty > 0 and despatch_qty = 0 and invoice_qty = 0 then narrative "Order Entered"
Else
If order_qty > 0 and despatch_qty = order_qty and invoice_qty = 0 then narrative "Order Fully Despatched"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty = 0 then narrative "Order Partially Despatched Despatched"
Else
If order_qty > 0 and despatch_qty =order_qty and invoice_qty = order_qty then narrative "Invoiced"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty = despatch_qty then narrative "Order Partially Despatched But Despatches Fully Invoiced"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty >0 and invoice_qty < despatch_qty then narrative "Order Partially Despatched and Despatches Partially Invoiced"
Else "Unknown Status"
Any suggestions gratefully received
I need to return an 'order status' field narrative based upon nested if and statements but cannot get this to work in SQL - I belive the CASE funtion should do the trick but have had no luck.
In short the narrative to be returned would be;
If order_qty > 0 and despatch_qty = 0 and invoice_qty = 0 then narrative "Order Entered"
Else
If order_qty > 0 and despatch_qty = order_qty and invoice_qty = 0 then narrative "Order Fully Despatched"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty = 0 then narrative "Order Partially Despatched Despatched"
Else
If order_qty > 0 and despatch_qty =order_qty and invoice_qty = order_qty then narrative "Invoiced"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty = despatch_qty then narrative "Order Partially Despatched But Despatches Fully Invoiced"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty >0 and invoice_qty < despatch_qty then narrative "Order Partially Despatched and Despatches Partially Invoiced"
Else "Unknown Status"
Any suggestions gratefully received