June 17, 2008 at 2:51 am
I have a Products Table (which contains inter alia ProductName and the respective CostPrice) that is related to PurchaseOrdersDetails Table and GoodsReceivedDetails Table.
The CostPrice column on PurchaseOrderDetails Table is derived from ProductName in Products but can be edited by users as prices change.
The CostPrice column on GoodsReceivedDetails is derived from the respective PurChaseOrderNo in PurchaseOrdersMaster Table but can again be edited by users as prices change.
When goods are received StockInHand column on Products Table is incremented.
The Menu Table holds details of all dishes and the Ingredients Table is a junction table between Products and Menu Tables, holding list of ingredients (ProductName) for each dish in Menu.
Using this structure, I am able to work backward and compute the cost of each dish using its Ingredients.ProductName and Products.CostPrice.
However, the challenge I now face is to extract the "actual cost" of the Ingredient by working backwards from the StockInHand to arrive at the required CostPrice from the GoodsReceivedDetails Table. For example, if a Hamburger in the Menu Table has Buns and BeefPatties listed as its ingredients in the Ingredients Table, and the Products.StockInHand is 50 for Buns and 90 for BeefPatties, I have to work backward to find the GoodsReceivedDetails.CostPrice for the 50th most recent Buns received and the 90th most recent BeefPatties.
Has anyone ever faced such a challenge? How can it be solved?
June 18, 2008 at 2:59 am
Maybe a script of the tables in question will help me get some answers:
[Code]CREATE TABLE [dbo].[MENU] (
[FOODID] [numeric](10, 0) NOT NULL ,
[DESC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COST] [numeric](15, 3) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PRODUCTS] (
[PRODID] [int] IDENTITY (1, 1) NOT NULL ,
[NAME] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PRICE] [numeric](18, 0) NOT NULL ,
[BALANCE] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INGREDIENTS] (
[FOODID] [numeric](10, 0) NULL ,
[PRODID] [numeric](18, 0) NULL ,
[QTY] [numeric](15, 3) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[GOODSRECEIVEDDETAILS] (
[GRNID] [int] NULL ,
[PRODID] [int] NULL ,
[COST] [money] NULL ,
[QTY] [decimal](8, 0) NULL
) ON [PRIMARY]
GO[/Code]
I guess I will have to obtain the value of PRODUCTS.BALANCE to use in the WHERE clause of
[Code]SELECT GR.COST
FROM GOODSRECEIVEDDETAILS GR
WHERE @VAR --@VAR = PRODUCTS.BALANCE
ORDER BY GR.GRNID DESC[/Code]
in order to get the actual cost price of the last existing item.
I can see it will require some subqueries, some dynamic TSQL, perhaps a TOP n clause. I will appreciate any help in getting me started.
June 18, 2008 at 8:27 am
So say the moxt recent 30 buns cost 3 each, and the 30 before that cost 2. Total cost for all these is 30x3 + 30x2 = 150.
But you've only got 50 buns, so you've already used 10 of the buns that cost 2, so the actual cost for your 50 buns would be 30x3 + 20x2 = 130.
Is that what you mean?
June 18, 2008 at 9:08 am
There's going to be a problem here. If there's no place to reference a date associated with the goods being received, you're in trouble, as TOP n queries cannot guarantee the order in which the records appear to have the top n records selected, unless there's an ORDER BY clause. I didn't see a date field in your tables, so there's just no way to be sure you have the last n items accurately costed without one.
It may work, but you wouldn't want to rely on it, because you'd have no way to tell if it wasn't working the way you want it to.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2008 at 1:03 am
[Quote]THood:
So say the moxt recent 30 buns cost 3 each, and the 30 before that cost 2. Total cost for all these is 30x3 + 30x2 = 150.
But you've only got 50 buns, so you've already used 10 of the buns that cost 2, so the actual cost for your 50 buns would be 30x3 + 20x2 = 130.
Is that what you mean?[/Quote]
You are almost there; my peculiar task requires costing food sold in any one day. so if 15 hamburgers were sold yesterday, their buns would be costed at an average of 2: ((2 x 15) / 15); and if the next day 20 were sold, their buns would be costed at an average of 2.75 : (((2 x 5) + (3 x 15)) / 20).
[Quote]Steve(aka smunson):
There's going to be a problem here.
If there's no place to reference a date associated with the goods being received, you're in trouble, as TOP n queries cannot guarantee the order in which the records appear to have the top n records selected, unless there's an ORDER BY clause. I didn't see a date field in your tables, so there's just no way to be sure you have the last n items accurately costed without one.
It may work, but you wouldn't want to rely on it, because you'd have no way to tell if it wasn't working the way you want it to.[/Quote]
Thank you for your hawk-eyedness; actually, there is a table that holds header details for each GRN:
[Code]CREATE TABLE [dbo].[GOODSRECEIVEDMASTER] (
[GRNDATE] [datetime] NULL ,
[GRNID] [numeric](8, 0) NOT NULL ,
[POID] [numeric](8, 0) NULL ,
[SUPPID] [numeric](8, 0) NULL ,
[INVNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCID] [numeric](8, 0) NULL ,
) ON [PRIMARY][/Code]
I can now foresee an unexpected problem: the Sales table has triggers to deduct the underlying ingredients stock-in-hand balance in PRODUCTS Table as soon as a sale is inserted; in THood's example above therefore, if we sold 15 hamburgers yesterday and the balance of buns this morning is 50, we have to extract the cost of 15 buns from the 65th recent-most to the 51st recent-most, as the 50 on hand have not been used up yet.
June 19, 2008 at 4:25 am
So we want sales for a particular day, and we've got a date to order by in that master table. The trigger isn't a problem. I've been thinking all along that you can do this by traversing the rows in GoodsRecievedDetails, adding up values of Qty until you reach the row with the (in this case) 51st most recent. It's then just a matter of calculating how many of the buns you sold should be costed at the price in this row, then moving down to the next row and calculating how many here, and so on until you reach the row with the 65th most recent.
This will work if you run it end of day or very next morning before any more sales, but if you want to be able to do it for any given day it gets more complicated. You'd have to calculate what the StockInHand was on that day (total stock delivered up to that day minus total sales up to that day? I think this is the real problem, what effect does having sales and deliveries on the same day have? you'd need to include the time as well as the date), traverse the rows till the first delivery on or before that day, then start your running total and calculations - it's much more horrible.
But there is another option.
I don't know if this is an option open to you, but it might be better to treat this in the same way as the StockInHand deductions: trigger to calculate it when a sale is made and store it in a table. As well as updating StockInHand with the delivery and sales quantities, you'd have a new field in the deliveries table that gets incremented when a sale is made - you update the least recent row where this new value is less than the delivery quantity. You also have a new table to store the calculated actual cost in, linked to the sales table.
Of course, it means you can only get figures from implementation onwards, and you'd have to set the new field in the least recent delivery by hand initially (so it's set to the right level for the current stock), but you only need some fairly simple finds and calculations.
Tom
June 19, 2008 at 7:52 am
June 19, 2008 at 8:23 am
Looks to me that you'd have a much easier time if there were a flag to tag lineitems that have been entirely used up, or alternatively, something to how much HASN'T been used yet. Calculating your stock on hand by going through all of your intentory entries from the begining of time until now is, well - a costly proposition.
this would be one of those things I'd be running past an accountant, because this looks to me to be the textbook case for using average cost as your cost basis, which would be a substantially easier calculation than trying to expire specific lots.
That being said - the process for expiring specific lots is essentially a running totals exercise. I know I refer to this article a lot, but it's essentially what you're looking for, and doesn't involve a cursor.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]
you're essentially doing a running total by product from oldest to newest, and comparing that to the items used for the day. If the items <running total, "consume" then entire lot, otherwise consume what's needed to get to the items used that day.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 19, 2008 at 8:58 am
Thanks, Matt for the nudge. I think I was one of the first members to read that article by Jeff in January but to be entirely honest, it did not make much sense to me at the time (I am a relative newbie compared to all you ninjas!). I will try reading it more calmly this time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply