Dear @emre, please correct me if I am wrong… (as you know this is an excercise that I will finish no matter what, LOL)…
I created a View that has two things…
- Inventory items with last know (last work period) quantities
- Last purchases
so, I stored that view… nice piece of SQL…
Now to get the real on line in the fly at the moment stock I just need to substract orders, add canceled orders and add voided orders…
is that correct???
I know this has a mayor drawback at the moment and that is RECEIPTS… I am working on it, but at the moment I am really interested in Inventory items specially Beverages (one to one). I have some issues there when whe have some soccer matches, we sell 80 bottles in 30 minutes… and need to have a very strict control…
This is the VIEW CODE:
CREATE VIEW Stock as
SELECT dbo.PeriodicConsumptionItems.InventoryItemName, CASE WHEN [BaseUnit] = [TransactionUnit] THEN CONVERT(INT, ISNULL([PhysicalInventory],
([InStock] + [Added] - [Removed] - [Consumption])) + ISNULL
((SELECT TOP 1 [Quantity]
FROM [InventoryTransactions]
WHERE [InventoryItem_Id] = [PeriodicConsumptionItems].InventoryItemId AND [Date] >= CONVERT(varchar(10), GETDATE(), 121)
ORDER BY [Id] DESC), 0)) ELSE CONVERT(INT, (CONVERT(numeric(6, 2), ISNULL([PhysicalInventory], ([InStock] + [Added] - [Removed] - [Consumption]))
+ ISNULL
((SELECT TOP 1 [Quantity]
FROM [InventoryTransactions]
WHERE [InventoryItem_Id] = [PeriodicConsumptionItems].InventoryItemId AND [Date] >= CONVERT(varchar(10), GETDATE(), 121)
ORDER BY [Id] DESC), 0)) * [UnitMultiplier])) END AS Stockfisico, dbo.InventoryItems.BaseUnit AS Unidad, FORMAT(CONVERT(numeric(6, 2),
dbo.PeriodicConsumptionItems.Cost), 'C') AS CostoUnit, FORMAT(CONVERT(numeric(6, 2),
dbo.PeriodicConsumptionItems.Cost * (ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory,
dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption)
+ ISNULL
((SELECT TOP (1) Quantity
FROM dbo.InventoryTransactions
WHERE (InventoryItem_Id = dbo.PeriodicConsumptionItems.InventoryItemId) AND (Date >= CONVERT(varchar(10), GETDATE(), 121))
ORDER BY Id DESC), 0))), 'C') AS CostoTOT
FROM dbo.PeriodicConsumptionItems LEFT OUTER JOIN
dbo.InventoryItems ON dbo.PeriodicConsumptionItems.InventoryItemId = dbo.InventoryItems.Id
WHERE (dbo.PeriodicConsumptionItems.PeriodicConsumptionId =
(SELECT TOP (1) Id
FROM dbo.PeriodicConsumptions
ORDER BY Id DESC)) AND (CONVERT(numeric(6, 2), ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory,
dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption))
>= 0.05)
Thanks a lot, and sorry to bother you so much!!!
G.
