# ON THE FLY Stock

Dear @Emre… as you know I have been struggling with this since day 0… Please correct me if I am wrong but…

We have Last consolidated Stock:

``````SELECT [InventoryItemName],
CASE WHEN [BaseUnit] = [TransactionUnit] THEN
ELSE
END as Stockfisico,
FORMAT(CONVERT(numeric(6,2),[Cost]),'C') as CostoUnit,

FROM [PeriodicConsumptionItems]

LEFT OUTER JOIN [InventoryItems] ON [InventoryItemId] = [InventoryItems].[Id]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND

ORDER BY [InventoryItemName]
``````

We have Current sold Items (during a work Period):

``````SELECT [MenuItems].[GroupCode],
[MenuItemName]+(CASE WHEN [PortionName] = 'Normal' THEN '' ELSE ' ' + [PortionName] END),

FROM [Orders]

WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] < '{End}' AND DecreaseInventory = 1 AND CalculatePrice <> 0

GROUP BY [MenuItems].[GroupCode],[MenuItemName]+(CASE WHEN [PortionName] = 'Normal' THEN '' ELSE ' ' + [PortionName] END)

``````

So what is stopping as from merging thos two reports and get the ON THE FLY Stock report?

Thanks!!!

G.

You should be able to do this if you have column names that are the same, something like:

``````SELECT ItemName, sum(a), sum(b), sum(c) FROM (
SELECT StockItem as ItemName, blah1 as a, blah2 as b, blah3 as c FROM StockItems
UNION
SELECT SoldItem as ItemName, -1*foo1 as a, -1*foo2 as b, -1*foo3 as c FROM SoldItems
)
GROUP BY ItemName
``````

For this to work, your Product Names would need to be the same as your Inventory Names.

EDIT: But I see another issue… Consumption is based on Recipes, so selling a Product isn’t going to tell you how much of a Inventory Item has been depleted, unless you do a bunch of work reading Recipes by Product and tally all of that up.