[CORRECTED] Stock Report using SQL

Version 4 V4 Custom Reports
1

After all the discussion we had with @emre, @Jesse and @QMcKay I finally got together the final (I beleive) version of ths stock report.

This Report will show Inventory Items in BASE UNIT (converted from TRANS UNIT if different) and Cost related to each product, and a total of how much money you have sitting in stock.

The Report:

[Stock:2, 1,1, 1, 1]
>Item|Stock Fisico|UNI|Costo Uni|Costo TOT
@@Stock
>TOTAL||||@@TotalStock

The SQL:

SELECT [InventoryItemName],
      CONVERT(INT,ROUND(CASE WHEN [BaseUnit] = [TransactionUnit] THEN
        (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
        ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))*[UnitMultiplier]
	  END,0)) as Stockfisico,
	  [BaseUnit] as Unidad,
	  CONVERT(numeric(6,2),([Cost]/[UnitMultiplier])) as CostoUnit,
	  CONVERT(numeric(6,2),ROUND([Cost]*ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])),2)) as CostoTOT
      
FROM [PeriodicConsumptionItems]

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

WHERE [PeriodicConsumptionId] = (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]

And

SELECT SUM(CONVERT(numeric(6,2),ROUND([Cost]*ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])),2))) as Costo
      
FROM [PeriodicConsumptionItems]

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

Hope this is usefull!!! and sorry for so many versions!!!

G.

2 Likes