[quote="gerlandog, post:1, topic:2360, full:true"]
Hope that works for everyone....
Report:
[Stock:2, 1, 1, 1]
>Item|Stock Fisico|Stock UNI|Costo
@@Stock
>TOTAL|||@@TotalStock
SQL @@Stock:
SELECT [InventoryItemName],
([InStock]+[Added]-[Removed]-[Consumption]) as StockFisico,
CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) as StockFisicoUN,
CONVERT(numeric(6,2),([Cost]*([InStock]+[Added]-[Removed]-[Consumption]))) as Costo
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) >= 1
ORDER BY [InventoryItemId]
SQL @@TotalStock:
SELECT CONVERT(numeric(6,2),SUM(([Cost]*([InStock]+[Added]-[Removed]-[Consumption])))) as Costo
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) >= 1
[/quote]
if i run the above query in sql studio i am getting nothing.
SELECT [InventoryItemName],
([InStock]+[Added]-[Removed]-[Consumption]) as StockFisico,
CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) as StockFisicoUN,
CONVERT(numeric(6,2),([Cost]*([InStock]+[Added]-[Removed]-[Consumption]))) as Costo
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) >= 1
ORDER BY [InventoryItemId]
SELECT [InventoryItemName],
([InStock]+[Added]-[Removed]-[Consumption]) as StockFisico,
CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) as StockFisicoUN,
CONVERT(numeric(6,2),([Cost]*([InStock]+[Added]-[Removed]-[Consumption]))) as Costo
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [WarehouseConsumptionId] FROM [PeriodicConsumptionItems] ORDER BY [WarehouseConsumptionId] DESC)
ORDER BY [InventoryItemId]
This query reads data from PeriodicConsumptionItems table and data inserted into this table when you end work period. It does not shows real time inventory.
@madiha I am not trying to be mean but your questions often have hidden meaning that we find later in conversation. If you would take the time to explain your need fully from beginning we can avoid that confusion.