Continuing the discussion from [CORRECTED] Stock Report using SQL:
Hello Friends,
I’ve been trying to create a Report that shows Stock Value at Sale.
Item Name | Qty InStock | Selling Price | Total Value
I came up with the Query bellow but it is not giving me the correct InStock Qty.
SELECT
I.Name AS Item,
SUM(IT.Quantity) AS Qty,
PL.Price AS SellingPrice,
SUM(IT.Quantity) * PL.Price AS SalesValue
FROM dbo.InventoryTransactions IT
INNER JOIN dbo.InventoryItems I
ON I.Id = IT.InventoryItem_Id
INNER JOIN dbo.MenuItems MI
ON MI.Name = I.Name
INNER JOIN dbo.MenuItemPortions MP
ON MP.MenuItemId = MI.Id
INNER JOIN dbo.MenuItemPrices PL
ON PL.MenuItemPortionId = MP.Id
GROUP BY
I.Name,
PL.Price
HAVING SUM(IT.Quantity) > 0
ORDER BY I.Name;
Could someone help me achieve this type of a report