CODEWITHSUNDEEP
sqlsql-server
Hammas
Hammas

Reputation: 1214

Retrive total quantity of ordered Items using SQL

I am trying to get Quantity of an item that is requested and yet not received. Process of purchasing and Item is as
Created Purchase Order > Ordered Items saved in ItemsRequested table
Created Purchase Invoice > Received Items are in ReceivedItems table

Using below query to get Items Requested quantity but the result is wrong. What is logically wrong in query ? 

select Sum(ItemRequested.Quantity) as OnOrder from ItemRequested
Inner join PurchaseOrders on ItemRequested.PurchaseOrderId = PurchaseOrders.PurchaseOrderId
Inner join PurchaseInvoices on PurchaseInvoices.PurchaseOrderId = PurchaseOrders.PurchaseOrderId
inner join ItemReceived on ItemReceived.PurchaseInvoiceId = PurchaseInvoices.PurchaseInvoiceId
where PurchaseOrders.DateOrdered >'2019-04-15 11:37:26.537' and ItemRequested.ItemId=3439

enter image description here enter image description here enter image description here enter image description here

Upvotes: 0

Views: 508

Answers (1)

Olivier Depriester
Olivier Depriester

Reputation: 1625

The definition of the items you need is "requested but not yet received". So you have to retrieve the rows that are in ItemRequested but absent from ItemReceived. Thus your query should be more like this

SELECT  SUM(ItemRequested.Quantity) AS OnOrder 
FROM    ItemRequested
        INNER JOIN PurchaseOrders ON ItemRequested.PurchaseOrderId = PurchaseOrders.PurchaseOrderId
WHERE   PurchaseOrders.DateOrdered >'2019-04-15 11:37:26.537' 
AND     ItemRequested.ItemId=3439  
-- Exclude the PO having received items
AND NOT EXISTS (
    SELECT 1
    FROM PurchaseInvoices 
        INNER JOIN ItemReceived ON ItemReceived.PurchaseInvoiceId = PurchaseInvoices.PurchaseInvoiceId
    WHERE PurchaseInvoices.PurchaseOrderId = PurchaseOrders.PurchaseOrderId
)

Upvotes: 1

Related Questions