I am having a problem reconciling the change in inventory value from month to month.
For example, my item value list at 3/31 is 100,000.00. My item value list at 4/30 is 125,000.00. I would expect to see a report that has everything that was received in April and the COGS for everything that was sold or transferred out in April.
I do not see any reports that do this, nor do I see an Items Received listing that shows the total cost of items received into inventory from each vendor during the month. Am I missing something?
I have written the following to try to get the information I need. Does anyone have anything better ? or does it look like I am missing something?
SELECT PurchaseOrder.PONumber,
CONVERT(nvarchar, InventoryTransferLog.DateTransferred, 101),
Supplier.SupplierName,
SUM(CONVERT(decimal(28,2),(PurchaseOrderEntry.Price * InventoryTransferLog.Quantity)/(PurchaseOrder.ExchangeRate)))
FROM InventoryTransferLog LEFT JOIN PurchaseOrder ON InventoryTransferLog.ReferenceID = PurchaseOrder.ID
LEFT JOIN PurchaseOrderEntry ON InventoryTransferLog.ReferenceEntryID PurchaseOrderEntry.ID
LEFT JOIN Item ON InventoryTransferLog.ItemID = Item.ID
LEFT JOIN Department ON Item.DepartmentID = Department.ID
LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID
WHERE InventoryTransferLog.Type = 1
AND CONVERT(nvarchar, InventoryTransferLog.DateTransferred, 101) > ?03/31/2007?
GROUP BY CONVERT(nvarchar, InventoryTransferLog.DateTransferred, 101), PurchaseOrder.PONumber, Supplier.SupplierName UNION
SELECT 'COGS',
CONVERT(nvarchar, [Transaction].Time, 101),
'COGS',
( SUM(CONVERT(decimal(28,2),( TransactionEntry.Cost * TransactionEntry.Quantity))) ) * -1
FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID
LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
WHERE CONVERT(nvarchar, [Transaction].Time, 101) > ?03/31/2007?
Group By CONVERT(nvarchar, [Transaction].Time, 101)