SQL Report Challenge

V5 Question
1 
>>Product|QTY|Price
{REPORT SQL DETAILS:
SELECT 
product.[Product Name] AS ItemName, 
inventory.[Current Stock] AS QtyInStock, 
price.[Price] AS SellingPrice
FROM [Products]

}```

Hey guys trying to create a Report that will show the product with its qty and selling price but is coming up blank.
2

There is no table “Products”. Is this something custom you created or are you working with a different POS?

3

Eish!! Didnt notice that. No am using SambaPOS V5. Let me revisit this and make changes. I will update if anything

4 
SELECT
    MenuItems.GroupCode,
    MenuItems.Name,
    MenuItemPrices.Price,
    InventoryTransactions.Quantity
FROM
    MenuItems
INNER JOIN MenuItemPrices ON MenuItems.Id = MenuItemPrices.Id
INNER JOIN InventoryTransactions ON MenuItems.Id = InventoryTransactions.Id

I Edited my SQL to this above but still having a challenge when I take it to SambaPOS it’s showing the Price twice but not showing the QTY. But when I run the Query in SQL its working fine.
Not sure what I have missed.

5

I have used the Script and call method and use thie query below

SELECT
    MenuItems.GroupCode,
    MenuItems.Name,
    CAST(SUM(MenuItemPrices.Price) AS INT) AS TotalPrice,
    CAST(SUM(InventoryTransactions.Quantity) AS INT) AS TotalQuantity,
    CAST(SUM(MenuItemPrices.Price * InventoryTransactions.Quantity) AS INT) AS TotalCost
FROM
    MenuItems
INNER JOIN MenuItemPrices ON MenuItems.Id = MenuItemPrices.Id
INNER JOIN InventoryTransactions ON MenuItems.Id = InventoryTransactions.Id
GROUP BY
    MenuItems.GroupCode,
    MenuItems.Name;

Add its working fine but how do I add the Totals at the bottom?

image
image691Ă—458 50.4 KB

6

For totals you’ll have to split into two separate queries. Seeing as this is for reporting, I’m not too worried about hitting the db multiple times.

I see you’re casting totals as integers. Is your currency, item quantity, and inventory cost always in whole numbers?

enumerated totals:

SELECT mi.GroupCode,
       mi.Name,
       CAST(SUM(mipr.Price) AS INT) AS TotalPrice,
       CAST(SUM(it.Quantity) AS INT) AS TotalQuantity,
       CAST(SUM(mipr.Price * it.Quantity) AS INT) AS TotalCost
FROM dbo.MenuItems mi
    INNER JOIN dbo.MenuItemPrices mipr
        ON mi.Id = mipr.Id
    INNER JOIN dbo.InventoryTransactions it
        ON mi.Id = it.Id
GROUP BY mi.GroupCode,
         mi.Name
ORDER BY mi.GroupCode,
         mi.Name;

grand totals:

SELECT 'Totals',
       NULL,
       CAST(SUM(mipr.Price) AS INT) AS TotalPrice,
       CAST(SUM(it.Quantity) AS INT) AS TotalQuantity,
       CAST(SUM(mipr.Price * it.Quantity) AS INT) AS TotalCost
FROM dbo.MenuItems mi
    INNER JOIN dbo.MenuItemPrices mipr
        ON mi.Id = mipr.Id
    INNER JOIN dbo.InventoryTransactions it
        ON mi.Id = it.Id;
2 Likes
7

Hey this worked thanks… But for season reason it’s not picking the quantity currently in the Warehouse as closing quantity. Or perhaps I picked a wrong table that holds the Prediction quantity in the Warehouse.
Any Idea which Table holds that information?

8

I’m not really familiar with how SambaPOS stores that. I haven’t been down that rabbit hole yet.

Looking at the db schema, some combination of the WarehouseConsumptions, PeriodicConsumptions, and PeriodicConsumptionItems tables is where I would start looking.

9

Hey thanks for the info, The report ready thanks to you. I will check through tables and keep you posted.