CODEWITHSUNDEEP
sqlsqlite
Roger
Roger

Reputation: 597

Query for remaining balance

Query for remaining balance

I am using SQLITE 3.1.1

The scenario is the ff: Let us say Total Quantity is 11.

enter image description here

The formula should be:

Total Quantity - Quantity Used = Remaining

It should look like this:

Expected Result:

enter image description here

Also, Remaining value can't be lower than 0.

I currently have this SQL query but it doesn't get the Remaining query result for the next transaction but rather it always starts with Total Quantity.

SELECT
    filter_maintenance.maintenance_id,
    filter_maintenance.stock_id, 
    filter_maintenance.quantity_used,
    filter_maintenance.date_registered,
    filter_maintenance.date_changed,
    inventories.stock_name,
    SUM(inventories_order.order_quantity) - filter_maintenance.quantity_used AS Remaining
FROM filter_maintenance
INNER JOIN inventories ON filter_maintenance.stock_id = inventories.stock_id
INNER JOIN inventories_order ON filter_maintenance.stock_id = inventories_order.stock_id
GROUP BY filter_maintenance.maintenance_id

This is the output I currently have:

enter image description here

Your help is greatly appreciated. Thank you in advance.

Upvotes: 1

Views: 348

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Since you are using sqllite and there are no window functions you need to use a self-join instead. I assume maintenance_id is a primary key in filter_maintenance.

SELECT
    filter_maintenance.maintenance_id,
    filter_maintenance.stock_id, 
    filter_maintenance.quantity_used,
    filter_maintenance.date_registered,
    filter_maintenance.date_changed,
    inventories.stock_name,
    sum(inventories_order.order_quantity) - filter_maintenance.sum_quantity_used AS Remaining
FROM 
(
    SELECT  fm1.*,
            sum(fm2.quantity_used) AS sum_quantity_used 
    FROM filter_maintenance fm1
    INNER JOIN filter_maintenance fm2 ON fm1.stock_id = fm2.stock_id and
                                         fm1.date_registered >= fm2.date_registered
    GROUP BY fm1.maintenance_id
) filter_maintenance
INNER JOIN inventories ON filter_maintenance.stock_id = inventories.stock_id
INNER JOIN inventories_order ON filter_maintenance.stock_id = inventories_order.stock_id
GROUP BY filter_maintenance.maintenance_id

Upvotes: 1

Related Questions