[CORRECTED] Stock Report using SQL --- arithmetic overflow

Continuing the discussion from [SambaPOS 5.1.53 Release](http://forum.sambapos.com/t/sambapos-5-1-53-release/7550):


Continuing the discussion from [[CORRECTED] Stock Report using SQL](http://forum.sambapos.com/t/corrected-stock-report-using-sql/3086):

[quote="gerlandog, post:1, topic:3086, full:true"]
After all the discussion we had with @emre, @kendash 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.
[/quote]

Continuing the discussion from [[CORRECTED] Stock Report using SQL](http://forum.sambapos.com/t/corrected-stock-report-using-sql/3086):

[quote="gerlandog, post:1, topic:3086, full:true"]
After all the discussion we had with @emre, @kendash 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.
[/quote]

this query was giving me error of arithmetic overflow from converting numeric to numberic data type

so what i did

i change numeric(6,2) to INT in the above query

then the query was working great

1 Like

Hi, guys and @gerlandog, I need a consumption report for v4, Current I have it working perfectly working in v5 as in the screenshot, I know in v4 we use custom report, has someone worked on this, Kindly point it out

The tags to build that are not available in v4 they are v5 only. A true consumption report was not easily done in v4 it lacked features that were solved with v5