My client brought this to my attention. When you add up the quantities of the products sold the ones that we looked at (Top) doesn’t add correctly. We are trying to understand why this would be.
Savanna Dry 330ml (6 pack) sold 611 QTY x 150 (Selling Price) = 91650 (On report = 77850)
Corona 355ml (6 pack) sold 432 QTY x 160 (Selling Price) = 69120 (On report = 35200)
The database is SQL server express 2019. Database size without compression 7.8GB. They have been using SambaPOS 5 since 2018. The ticket records in the DB table = 561000+ and the orders records in the DB table = 1111541.
Can this be a result of too big DB? I upgraded them about 1 month ago to SambaPOS 5.3.6. They have 6 departments.
Hookah bar, (1 x Terminal)
Bottle bar, (1 x Terminal)
Bar, (10 x Terminal)
Kitchen, (3 x Terminal)
KDisplay (Kitchen displays), - 2 x Terminals (1 x Kitchen, 1 x Customer display)
VIP Customers (6 x Terminals)
Network is on gigabit network.
We cannot really determine when the calculations have not been accurate, they have only recently picked up this problem.
Database size and the network wouldn’t affect anything like this.
If that’s the default Item Sales Report then the totals in the right most column are from the orders table, not any of the price definitions. The price in the orders table is set by the price of the menu item on the ticket.
My guess is some prices on the ticket aren’t what they should be.
If you’re not using any custom price definitions, run this script in SSMS and it will give you a list of orders where the ticket price is less than the defined price.
SELECT o.TicketId,
t.TicketNumber,
o.CreatedDateTime,
o.MenuItemName,
o.Price AS SalePrice,
mipr.Price AS OriginalPrice,
o.CreatingUserName
FROM dbo.Orders o
INNER JOIN dbo.Tickets t
ON t.Id = o.TicketId
INNER JOIN dbo.MenuItems mi
ON mi.Id = o.MenuItemId
INNER JOIN dbo.MenuItemPortions mip
ON mip.Name = o.PortionName
AND mip.MenuItemId = o.MenuItemId
INNER JOIN dbo.MenuItemPrices mipr
ON mip.Id = mipr.MenuItemPortionId
WHERE o.Price < mipr.Price
ORDER BY o.TicketId;
DECLARE @Start DATETIME = '2021-12-25 14:23';
DECLARE @End DATETIME = '2021-12-26 13:17';
SELECT SUM(Quantity),
MenuItemName,
SUM(Price * Quantity)
FROM dbo.Orders
WHERE CreatedDateTime
BETWEEN @Start AND @End
GROUP BY MenuItemName
ORDER BY MenuItemName;
I saw that when I add the line WHERE … AND DecreaseInventory = 1 then the Orders are closer to the actual. I see that not all Orders has DecreaseInventory and I’m not sure why.
Report prior to 5.3.6? I cannot tell you now. I have a few older DB backups but to get the SambaPOS 5 version that worked with that DB version would be a mission.
Strange thing I noticed is that my other departments, Kitchen and Hookah calculates correctly. Kitchen works with weighed meat and chicken wings that they BBQ for the clients. The reason I mention this is that the sale report only shows whole amounts, not ex. 109,8991 or whatever this is. So the kitchen sales is always a few cents out because of the whole number.
I’m trying to build a custom report now for this but I’m totally lost with custom reports.
Hopefully someone else can give more clarity and in the mean time I just want to get more accurate data.
Thank you for the help so far. Your queries was a big help as I did all of the queries one by one and then tried to compare.
Loaded the db into 5.3.0 and this is the result from the last full work period:
Restore the same db you sent me into 5.3.6 and then execute DELETE FROM dbo.WorkPeriods WHERE Id = 3650 in SSMS and then rerun Item Sales Report. Let’s see what it shows on your end now that we’re working with the same data.
I have to go water the dog and will check back in a bit.
Thank you @Jesse. Just checked now and I do see a lot of Gift transactions in the workperiod. Do you have a SQL script that I can run to see who these users was?
