Android Question SQL Query Question

[dw_b4x]

I have an SQL Query challenge.

Background - I have removed all the extra fields giving only needed items. In reality there are 4 PO types,2 stores

We have 2 Tables
Inventory ( INVENTORY )
inv_filecode_short
inv_part_number
inv_part_description
inv_current_inventory
inv_reorder_qty

PO ( PURCHASE ORDERS )
po_inv_filecode_short
po_part_number
po_description
po_type (can be either 'STK' or 'BO'
po_store1_qty_order

Using this SQL statement - I am able to generate are report that shows all items on order. (This gives me calculated field reflecting the qty on order STK and BO. The reason I am using the Sum function is to take into account the fact that an item can be on multiple POs.

Select IT.inv_filecode_short,IT.inv_part_number,IT.inv_part_description,IT.inv_current_inventory,IT.inv_reorder_qty,
SUM(DISTINCT CASE WHEN PT.po_type='STK' and .po_inv_filecode_short=IT.inv_filecode_short THEN PT.po_store1_qty_order ELSE 0 END) AS sum_store1_qty_order_stock,
SUM(DISTINCT CASE WHEN PT.po_type='BO' and PT.po_inv_filecode_short=IT.inv_filecode_short THEN PT.po_store1_qty_order ELSE 0 END) AS sum_store1_qty_order_bo
FROM INVENTORY AS IT PO AS PT
INNER JOIN PO ON IT.inv_filecode_short=PT.po_aw_filecode_short
GROUP BY IT.inv_part_number"

However what I need to do is create a reorder query reflecting the current inventory level vs the reorder point and then automatically factor any item that are on STK orders (the BO Items are not factored in the reorder calculation -- remember this is an short version of what I need There are actually 4 PO Types !! Plus Transfers,Customer Commits )

The reorder calculation would be similiar to this.

reorder_qty=(inv_reorder_point-inv_current_inventory)-STK_QTY_ORDER. Creating an recordset with a calculated Field would be ideal. This will allow me generate 'Working' POs
Plus limit the returned recordset to only items that have a reorder qty >0

Of course this is for the complete Inventory file.

I hope this explains the situation I am in. Any help or suggestions would be greatly appreciated.

 

[Lucas Siqueira]

Key steps:
Calculate the sum of po_store1_qty_order for STK type POs.
Calculate the reorder quantity based on the given formula: reorder_qty = (inv_reorder_qty - inv_current_inventory) - sum_store1_qty_order_stock.
Filter the results to include only items with a reorder_qty greater than 0.

Modified SQL:

SELECT 
    IT.inv_filecode_short,
    IT.inv_part_number,
    IT.inv_part_description,
    IT.inv_current_inventory,
    IT.inv_reorder_qty,
    COALESCE(SUM(CASE 
        WHEN PT.po_type = 'STK' AND PT.po_inv_filecode_short = IT.inv_filecode_short 
        THEN PT.po_store1_qty_order 
        ELSE 0 
    END), 0) AS sum_store1_qty_order_stock,
    (IT.inv_reorder_qty - IT.inv_current_inventory - COALESCE(SUM(CASE 
        WHEN PT.po_type = 'STK' AND PT.po_inv_filecode_short = IT.inv_filecode_short 
        THEN PT.po_store1_qty_order 
        ELSE 0 
    END), 0)) AS reorder_qty
FROM 
    INVENTORY AS IT
LEFT JOIN 
    PO AS PT ON IT.inv_filecode_short = PT.po_inv_filecode_short
GROUP BY 
    IT.inv_filecode_short,
    IT.inv_part_number,
    IT.inv_part_description,
    IT.inv_current_inventory,
    IT.inv_reorder_qty
HAVING 
    reorder_qty > 0

COALESCE Function: Used to handle cases where there might be no matching records in the PO table for a particular item in the INVENTORY table, ensuring the sum is treated as 0.

SUM Function: Calculating the sum of po_store1_qty_order for POs of type STK.

Reorder Quantity Calculation: Subtracting the sum of STK order quantities from the difference between inv_reorder_qty and inv_current_inventory.

HAVING Clause: Filtering to return only those items where the calculated reorder_qty is greater than 0.
This query should create a result set that meets your requirements, allowing you to generate working POs based on the inventory levels and outstanding STK orders.

 

[dw_b4x]

Thanks for the help. I will look into this. I will export a test set of data and test things out.
I may be a week or two. I am in the middle of trying to get some other changes processed.
I will definitely keep you informed.