Stock Value Report

V5 Question
1

Hello,

I’ve been able to create an SQL Query that gives me the Stock Value both at Cost as well as at Sale.
Could someone help me create a report from it.

image
image1308×836 133 KB

Use SambaPOS5

SELECT        TOP (100) PERCENT dbo.PeriodicConsumptionItems.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 InStock, dbo.InventoryItems.BaseUnit AS Unit,
                                 
                         CONVERT(numeric(18, 2), 
                         dbo.PeriodicConsumptionItems.Cost / dbo.PeriodicConsumptionItems.UnitMultiplier) AS UnitCostPrice,
                         
                         CONVERT(numeric(18, 2), 
                         ROUND(dbo.PeriodicConsumptionItems.Cost * ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory, 
                         dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption), 2)) AS TotalCostValue, dbo.MenuItemPrices.Price as UnitSellingPrice,
                         
                         CONVERT(numeric(18, 2), 
                         ROUND(dbo.MenuItemPrices.Price * ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory, 
                         dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption), 2)) AS TotalSellingValue

FROM            dbo.PeriodicConsumptionItems INNER JOIN
                         dbo.MenuItemPrices ON dbo.PeriodicConsumptionItems.Id = dbo.MenuItemPrices.Id LEFT OUTER JOIN
                         dbo.InventoryItems ON dbo.PeriodicConsumptionItems.InventoryItemId = dbo.InventoryItems.Id
WHERE        (dbo.PeriodicConsumptionItems.PeriodicConsumptionId =
                             (SELECT        TOP (1) Id
                               FROM         dbo.PeriodicConsumptions
                               ORDER BY Id DESC)) AND (CONVERT(numeric(18, 2), ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory, 
                         dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption)) >= 0.05)
ORDER BY dbo.PeriodicConsumptionItems.InventoryItemName
2