CODEWITHSUNDEEP
mysqlsqljoincalculated-columns
Lewis Morris
Lewis Morris

Reputation: 2134

SQL Join table with calculated fields

I want to add another column to this query to show backorders that need to be processed.

Currently the table is showing all orders that have outstanding items and the in stock amount for each stock ref included.

I want to to add a field for suggested invoice amount. I.e if the order qty is 5 , the invoice qty is 2 and the in stock qty is 2 then suggest to deliver 2.

if the instock amount was 100 then suggest to deliver 3.

My current attempt is below but I cant work out how to do the last bit.

I know i need to do (qtyOrdered-qtyinvoiced) but literally hit a brick wall.

SELECT 
    c.cus_name,
    ol.orderLines_stockRef,
    ol.orderLines_qtyOrdered,
    ol.orderlines_qtyInvoiced,
    pr.products_instock
FROM
    cus c
        JOIN
    orderheader oh ON oh.orderHeader_customer = c.cus_id
        JOIN
    orderlines ol ON ol.orderLines_orderId = oh.orderHeader_id
        JOIN
    products pr ON ol.orderlines_stockref = pr.products_reference
WHERE
    ol.orderLines_qtyOrdered <> ol.orderlines_qtyInvoiced

Current output :

Output

Upvotes: 2

Views: 3291

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25361

Try this:

SELECT 
    c.cus_name,
    ol.orderLines_stockRef,
    ol.orderLines_qtyOrdered,
    ol.orderlines_qtyInvoiced,
    pr.products_instock,
    (CASE WHEN ol.orderLines_qtyOrdered - ol.orderlines_qtyInvoiced > pr.products_instock
          THEN pr.products_instock
          ELSE ol.orderLines_qtyOrdered - ol.orderlines_qtyInvoiced) AS qtyToSend,
FROM
    cus c
        JOIN
    orderheader oh ON oh.orderHeader_customer = c.cus_id
        JOIN
    orderlines ol ON ol.orderLines_orderId = oh.orderHeader_id
        JOIN
    products pr ON ol.orderlines_stockref = pr.products_reference
WHERE
    ol.orderLines_qtyOrdered <> ol.orderlines_qtyInvoiced

Upvotes: 1

Related Questions