CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayman
Ayman

Reputation: 872

Getting item current balance using SQL

How can I get certain item balance in certain store from below schema ? 

item balance = ItemOpeningBalances.OpeiningBalance + PurchaseOrderDetails.ReceivedQuantity

I have tried to get balance using below query but it gave me null value in balance

Query

SELECT
    i.ItemID,
    s.StoreID,
    SUM(iob.OpeiningBalance + pod.ReceivedQuantity) AS balance
FROM  
    dbo.StoreDetails st
LEFT JOIN 
    Stores s ON st.StoreID = s.StoreID
LEFT JOIN 
    PurchaseOrders po ON st.PurchaseOrderID = po.PurchaseOrderID
LEFT JOIN 
    PurchaseOrderDetails pod ON po.PurchaseOrderID = pod.PurchaseOrderID
LEFT JOIN 
    Items i ON pod.ItemID = i.ItemID
LEFT JOIN 
    ItemOpeningBalances iob ON st.OpeiningBalanceID = iob.OpeiningBalanceID
                            AND i.ItemID = iob.ItemID
WHERE 
    (i.ItemID = 1)
    AND (s.StoreID = 1)
GROUP BY 
    i.ItemID, s.StoreID

Output

ItemID StoreID balance
1      1       Null

Schema

enter image description here

Upvotes: 1

Views: 159

Answers (1)

Robert Columbia
Robert Columbia

Reputation: 6408

This may be happening because anything plus null is null.

Try this:

SELECT
  i.ItemID,
  s.StoreID,
  SUM(COALESCE(iob.OpeiningBalance,0) + COALESCE(pod.ReceivedQuantity,0) AS balance
FROM dbo.StoreDetails st
LEFT JOIN Stores s
  ON st.StoreID = s.StoreID
LEFT JOIN PurchaseOrders po
  ON st.PurchaseOrderID = po.PurchaseOrderID
LEFT JOIN PurchaseOrderDetails pod
  ON po.PurchaseOrderID = pod.PurchaseOrderID
LEFT JOIN Items i
  ON pod.ItemID = i.ItemID
LEFT JOIN ItemOpeningBalances iob
  ON st.OpeiningBalanceID = iob.OpeiningBalanceID
  AND i.ItemID = iob.ItemID
WHERE (i.ItemID = 1)
AND (s.StoreID = 1)
GROUP BY i.ItemID,
         s.StoreID

Upvotes: 1

Related Questions