ON THE FLY Stock

Version 4 V4 Question
1

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
        CONVERT(INT,ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))
      ELSE
        CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier]))
      END as Stockfisico,
	  [BaseUnit] as Unidad,
	  FORMAT(CONVERT(numeric(6,2),[Cost]),'C') as CostoUnit,
	  FORMAT(CONVERT(numeric(6,2),([Cost]*ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))),'C') as CostoTOT
      
FROM [PeriodicConsumptionItems]

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

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
      AND
	  CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05

ORDER BY [InventoryItemName]

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

SELECT [MenuItems].[GroupCode],
       [MenuItemName]+(CASE WHEN [PortionName] = 'Normal' THEN '' ELSE ' ' + [PortionName] END),
       CONVERT(INT,SUM([Quantity])) as Cantidad

FROM [Orders]

LEFT OUTER JOIN [MenuItems] on [Orders].[MenuItemId] = [MenuItems].[Id]

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)

ORDER BY Cantidad DESC

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

Thanks!!!

G.

2

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.