I'm new to SQL, and I'm trying to create a database to manage a small inventory. This is the structure of the db:

DatabaseStructure

I need to create a query that returns the total inventory per material. So, the first step would be to look up for all the batches associated with the material. Second, look up for all the movements associated with each batch. Then, sum the quantity associated with each movement, but depending on the movement type (If it is a good receipt is addition ( ), but if it is an inventory withdrawal is subtraction (-)).

Here is an example of the tables with sample data and the desired result.

Table Material

Table Batch

Table Movement

Expected query result - total inventory per material:

TotalInventory calculation: for Bottle there are two good receipts movements of 100 and three withdrawals of 20, 25 and 10. So, total inventory will be (100 100)-(20 25 10)=145.

Thanks for your help!

CodePudding user response：

select
    mat.MaterialDescription,
    sum(
        case mov.MovementType
            when 'Good receipt' then 1
            when 'Inventory withdrawal' then -1
            else 0 /* don't know what to do for other MovementTypes */
        end * mov.Quantity
    ) as TotalInventory
from
    Material as mat
    left join Batch as bat on bat.MaterialID = mat.MaterialID
    left join Movement as mov on mov.BatchID = bat.BatchID
group by
    mat.MaterialDescription
;