Modified and enhanced SQL for STOCK Report

Version 4 V4 Custom Reports
1

The difference between this query and the previous one is that this one reacts to the data entered in the Column Current Inventory in the End Of Day Records.

The next step will be trying to react to orders so you can see stock ON THE FLY.

SELECT [InventoryItemName],
      CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as StockFisico,
	  CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as StockFisicoUN,
      CONVERT(numeric(6,2),CONVERT(numeric(6,2),([Cost]*(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))))))) as Costo
      
FROM [PeriodicConsumptionItems]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
      AND
	  CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05

ORDER BY [InventoryItemId]

Hope this is usefull.

G.

1 Like
2

Excellent @gerlandog! I just started looking into Inventory and was having a bit of trouble understanding it… you’ve helped with my quest.

For English, with a couple columns added for unit indicators:

USE [SambaPOS4]
SELECT
  ii.[Name] as [Item]

, ii.[TransactionUnit] as [PurchaseUnit]
, CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as PhysicalPurchaseStock

, ii.[BaseUnit] as [SellUnit]
, CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as PhysicalSellStock


, CONVERT(numeric(6,2),CONVERT(numeric(6,2),([Cost]*(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))))))) as CostInStock

FROM [PeriodicConsumptionItems] pci
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]

WHERE [WarehouseConsumptionId] = (SELECT max([Id]) FROM [PeriodicConsumptions])
      AND
      CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05

ORDER BY [Item]

Gives results as:

Indications:
Barena: Purchased 1 Case of 24 Bottles (24), sold 0 Bottles, remaining 24 bottles
Coke Classic: Purchased 1 Case of 12 Cans (12), sold 3 Cans, remaining 9 cans
Coke Lite: Purchased 1 Case of 12 Cans (12), sold 0 Cans, remaining 12 cans
Fresca: Purchased 1 Case of 12 Bottles (12), sold 0 Bottles, remaining 12 bottles
Ginger Ale: Purchased 1 Case of 24 Cans (24), sold 1 Cans, remaining 12 cans
Imperial: Purchased 2 Cases of 24 Bottles (48), sold 2 Bottles, remaining 46 bottles
Salva Vida: Purchased 2 Cases of 24 Bottles (48), sold 2 Bottles, remaining 46 bottles
Sprite: Purchased 1 Case of 12 Cans (12), sold 0 Cans, remaining 12 cans
Tonic Water: Purchased 1 Case of 24 Cans (24), sold 0 Cans, remaining 24 cans
Water 1000 mL: Purchased 2 Cases of 6 Bottles (12), sold 2 Bottles, remaining 10 bottles
Water 500 mL: Purchased 1 Case of 12 Bottles (12), sold 3 Bottles, remaining 9 bottles

3

I am looking at this report… and I am not following how it is read? The PhysicalPurchaseStock … changes on every close of workperiod? But so does the PhysicalSellStock? I see the logic and its sound but the part I dont like is the Understood Case qty… To know that you had 12 to begin with and you see .83 you would have to do the math to = 3 A report to me is more useful if you do not have to do the math.

Maybe I am reading this wrong from what you intended. But what exactly does this report show that is useful other than Cost In Stock? Because the report doesn’t really show you how many sold, (that is understood based off a value that is not represented in the report). It also doesn’t really show you how many purchased.

Is this just to look at current stock and the current inventory $?

I could see using this in combination with other data to give you a estimated current inventory $ from last closed work period.
EDIT: Maybe that’s all it was intended to be and I was reading too far into it… if so i apologize. I was seeing if it was something I could use and I couldn’t figure out how it would be useful to me.

If I purchase the same item at two different prices then the cost portion of this formula is not accurate is it?

4

The query reads the “End of Day” records ([PeriodicConsumptionItems]) for the last closed WorkPeriod. At the close of a Workperiod, every Inventory Item that has a mapping to a defined Recipe will have 2 rows written to the table to record current state of Stock Quantities.  The the counts are current and accurate for only the last WorkPeriod. All previous WorkPeriods become useless/invalid (in essence). This is why we filter with:

WHERE [WarehouseConsumptionId] = (SELECT max([Id]) FROM [PeriodicConsumptions])

You’re correct, it doesn’t show items sold, but it is inferred if you know how many bottles are in a case. The Sell count could be calculated, for that WorkPeriod, but even in my example, the Item may not have any Consumption during the WorkPeriod, so the amount sold would be zero.

CONVERT(INT,[Added]*[UnitMultiplier]*[Consumption]) as Sold

So above, you can see there is inventory missing from many items, but only Water has sell-counts for the current WorkPeriod.

5

Good question, let me try it.

I originally bought 2 cases of Imperial for 420 each, total cost 480 (17.50 per bottle).
This WorkPeriod, I bout another case for 400 (16.66 per bottle), and sold 1 Bottle (shown on report).
In total I would have 72 bottles, but somewhere along the way, I’ve sold 5, leaving only 67 in stock.

420+420+400=1240 / 72 bottles gives average bottle cost of 17.22.
Having sold 5, this works out to 86.11.
1240-86.11=1153.88 … different from report showing 1169.19

6

CostItems table should contain the cost values calculated for related periodic consumption record.

7

Thanks @emre, we will need to amend this query to use those values.

8

I have always favored a First in First Out method for calculating cost of goods sold. But for sake of keeping things simple we should probably agree on one method :stuck_out_tongue: And that method should probably match the method @emre programmed in to be consistent.

Of course this isn’t exactly cost of goods sold. But it could easily be used to determine estimates.

[Beginning Inventory + Purchases (in dollars)] ÷ [Beginning Inventory + Purchases (in units)] = Average Cost per Unit

Do we have access to Beginning inventory? or Purchases?

EDIT: Sorry I am going little off topic now.

9

@QMcKay sorry for not being clear. CostItems table contains calculated cost values for portions. The cost value you’re using in SQL is value calculated for inventory item. Just wanted to let you know that.

10

@Jesse our cost calculation is a little complicated. I’m not able to prepare an exact formula for that but I’ll try to explain key points.

  1. We always calculate cost by using previous work period. I mean cost generates from average value of cost of items in stock and cost of new purchases.
  2. If you modify physical inventory we distribute difference to all portions that have that inventory item in their recipe. It is weighted by the recipe amount. I mean if you use meat more for a product difference will change cost more for that product.

As we fix cost for past and carry cost through work periods cost becomes accurate as purchase price increases does not change past cost values. I know how most cost calculation methods works and I’ve decided to use a custom calculation method that we improved for restaurants.

11

Yes I agree. I am sorry I was mostly thinking out loud I should not have posted that. I was thinking for my retail business. Since the current Cost system would be no use to me in retail.

12

Yes I think it won’t work fine for retail. Our method works like moving average cost method but it does not recalculate after every purchase. It recalculates on every work period end (scaled).

13

Very sorry for the confusion. I get ideas mid conversation in forums and sometimes I start discussing it when I should probably keep it for a different post.

14

This Report shows only SOLD items, after a work period is closed… I am working in a ON THE FLY solution, wich I will use frequently…

Why? well for a lot of reasons, but the most important is that, I am the owner, but I work as cashier, as waiter, and sometimes I help to put out and put in again tables and chairs… so I have a lot of time that I cannot see the fridges, besides that, as our Pizza is done in house and our bread too, two employees do have keys of the restaurant, and they go to work during day, so I check my stock everyday. As I am ussualy at the cash register, fridges are not a problem because I can see them, but sometimes when we are one or two waiters short I HAVE to go out, so my fridges become a nice target for waiters, kitchen employees and people that go into the restaurant just to ask a question… This is not a normal restaurant, with civilized people, indoors, here we work OUTSIDE (remember we do have 28 Celsius all year and it does not rain much). So we have 15 tables with at least 6 people in each one screaming, asking for pizza FAST, coming and going. In a normal saturday we sell 30 pizzas, 25 sandwiches and 10-12 dishes in a period of 2-3 hours. Last month I relaxed a little with stock because we had other issues to attend, and we lost R$200 in sodas… That is a LOT of money to loose…

Thanks!!!

G.

15

So what you are saying is this will allow you to see a running amended total so you know accurate stock level daily.
You mentioned you lost money in soda’s, was that money walking out the door or was it lost sales due to not ordering correctly?

If it was money walking out the door I do not see how knowing inventory level can change that… but it could certainly help with lost sales due to planning.

Sorry I am not criticizing your report its great! I am just trying to understand its usage and evaluate if it would be useful for me to use as well.

16

YES, If you are inside a work period it will give you LAST work periods stock, if you are outside a work perido it will give you ACTUAL STOCK.

AS I wrote before, I am working in a ON THE FLY STOCK SOLUTION.

YES, letterally walking out of the door…How? When I am not looking at the frdgdes or nearby, people open the fridge take a can of soda or a 2Lt Coke and “FORGET” to tell me… Sometimes when the guys come in during day to make pizza pie they take a soda, normally they would tell me, but sometimes not.

I just print out an invertory sheet, do the actual control against the STOCK in the fridges and then I leave the piece of paper where everyone can see it, so they KNOW that I actually do a control EVERYDAY!!! and I mean everyday, before I start and afgter I close. After closing I do it with the kitchen employess and the waites, becuase I give them the opportunity to come clean. sometimes happens that a table ordered a 2LT sprite and when the waiter carries that order they change the order to a 2LT GUARANA or COKE and the waiter in the rush forgets to tell me that… so those differences should be taken in account since they are sepparated stock items, they have different price and and buy them in defferent quantities…

G.

17

Ok so you were leaving out some information! Your real use for this is for determent. You are using it to deter theft by showing them the numbers.

I can see how that would be useful to your situation but not mine… Thank you, you answered my question.

18

you just read one part…

After closing work period, you will have an actual and REAL stock position, with cost. So you will know exactly waths in your fridge and how much money you have sitting there… at least that is soething that matters to me… I try to work with almost 0 stock chain (we are really close to JIT)… I want my money in my pocket and not sitting in a fridge…LOL

G.

19

I can see how it is useful for you. For me it really would not be. I do not operate on current inventory I operate with forecasting this includes how much inventory I keep.

The way I run my business looking at a report that shows ending inventory and a estimated cost does not help me much. I am sorry If i confused you or conveyed my intentions wrong. I was trying to understand how your report worked and figure out if I could use it. I did too much thinking out loud :stuck_out_tongue:

Basically what I am saying is… my sql skills are lacking and I am learning it. I learn faster if I look at examples and break it down vs learning from beginning. The way I learn is through deriving processes from finished work. I know sounds odd but I learn faster and get a stronger grasp if I disassemble finished product and its purpose vs learning from beginning.

That said… I am approaching the point where I want to work on my reports portion of Samba and I need to understand SQL first.

Bottom line now that I see what your report does and its purpose… I have deduced that it is not something I would implement in my system. So it may not sound like it… but you have helped me a lot :stuck_out_tongue:

20

WHERE I PUT THIS COMMANDS?