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
Expand|Select|Wrap|Line Numbers
- 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
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?