Hi All,
I have the following bit of code which looks at a parts list and then checks on incoming and outgoing movements in the next 60 days
-
SELECT pa.PartNumber
-
,pa.OnHandStockLevel
-
,(SELECT sum(pod.QuantityPurchased - pod.QuantityReceived)
-
FROM Purchase.PurchaseOrderDetails pod
-
WHERE pod.ReceiptStatusID NOT IN (3,4)
-
AND pod.PartID = pa.PartID
-
AND pod.ReceiptDate < (getdate()+60)) -
-
(SELECT sum(sod.QuantityOrdered - sod.QuantityDespatched)
-
FROM Sales.SalesOrderDetails sod
-
WHERE sod.DespatchStatusID NOT IN (3,2)
-
AND sod.PartID = pa.PartID
-
AND sod.OnHold = 0
-
AND sod.DespatchDate < (getdate()+60))
-
FROM Structure.Parts pa
-
This does pretty much what I want it to do except that there may well be no purchases or sales in the time frame.
In this case in need the result of each of the sub queries to be 0 rather than 'NULL' so that the calculation of purchase - sales will still work.
I have tried using a CASE statement but it returns an error each time.
Any suggestions?
