Asp Forum - FIFO Cost Calculation - microsoft.public.sqlserver.programming

Monty

3/26/2007 9:01:00 AM

Hello,

I'm attempting to calculate FIFO cost for outgoing items from my Inventory
Transactions table. The table has a TransactionType field that indicates the
transaction is either (1) items received (positive), (2) an adjustment
(positive or negative), or (3) a sale (negative). It also has an
AmountPerUnit field, which is the actual cost for incoming items and the
calculated FIFO cost for outgoing. I would like a way to calculate the FIFO
cost per unit for outgoing items. I have seen Mr. Celko's excellent, though
brief, article on FIFO and LIFO calculations
(http://www.dbazine.com/ofinterest/oi-articl...), but I haven't been
able to figure out how to apply it to my table.

Referring to my sample table and data below, I'd like to know how I can
calculate with SQL the FIFO cost for rows 3,6,7,8,9 (I've put in the values
I would expect to receive in each case, as calculated by hand in the comment
field). Also, notice row 8 has 150 outgoing items which is more than we
currently have in the system. It sounds nonsensical, but is actually quite
common in the real world to have a pallet of items arrive and start going
out the door before they've been entered into inventory. I've considered
either (A) calculating the unit cost based on whatever is left in inventory
or (B) calculating the cost for the last 150 items to come in, even though
technically some of those items were already counted and went out the door.
I'm leaning towards (B) because when row 9 comes in we're already at a
negative, so now we can't use (A), and only using (B) would be consistent.
Any ideas on that?

My simplified table and some sample data:

CREATE TABLE [dbo].[ItemTranx] (
[ID] [int] NOT NULL , --this is an identity col but I removed it for this
demo so i can specify the values
[ItemID] [int] NOT NULL , -- FK to Items table (for this sample they are
all the same item type)
[TransactionType] [smallint] NOT
NULL, --1=Received(+),2=Adjustment(+/-),3=Sold(-)
[Quantity] [decimal](18, 2) NOT NULL, -- positive for items received,
negative for outgoing
[AmountPerUnit] [decimal](18, 2) NULL, -- actual cost for incoming items,
FIFO for outgoing
[Date] [smalldatetime] NOT NULL,
[Comment] [nvarchar] (200) --just for clarity with this example
) ON [PRIMARY]

INSERT INTO [dbo].[ItemTranx] VALUES (1,1,1,50,12,'01-JAN-2007','+50 @ $12')
INSERT INTO [dbo].[ItemTranx] VALUES (2,1,2,100,10,'01-JAN-2007','+100 @
$10')
-- How to find FIFO cost (expecting 11.33) for AmountPerUnit in this line?:
INSERT INTO [dbo].[ItemTranx] VALUES (3,1,2,-75,11.33,'01-JAN-2007','-75 @
$11.33 ((50*12)+(25*10))/75')
INSERT INTO [dbo].[ItemTranx] VALUES (4,1,1,100,14,'01-JAN-2007','+100 @
$14')
INSERT INTO [dbo].[ItemTranx] VALUES (5,1,1,100,16,'01-JAN-2007','+100 @
$16')
-- How to find FIFO cost (expecting 11.60) for AmountPerUnit in this line?:
INSERT INTO [dbo].[ItemTranx] VALUES (6,1,3,-125,11.60,'01-JAN-2007','-125 @
$11.60 ((75*10)+(50*14))/125')
-- How to find FIFO cost (expecting 15.20) for AmountPerUnit in this line?:
INSERT INTO [dbo].[ItemTranx] VALUES (7,1,3,-125,15.20,'01-JAN-2007', '-125
@ $15.20 ((50*14)+(75*16))/125')
INSERT INTO [dbo].[ItemTranx] VALUES (8,1,2,-150,18.40,'01-JAN-2007','-150 -
How to calculate, as we only have 25 in stock??')
INSERT INTO [dbo].[ItemTranx] VALUES (9,1,3,-75,18.40,'01-JAN-2007','How to
calculate, we have less than 0 in stock?')

Thank you for your time and your insight,
-Monty

8 Answers

Monty

3/26/2007 5:21:00 PM

Just to simplify this, ignore the question about the negative inventory for
now. I think I've found a relatively elegant solution to prevent that
situation from happening. So, my question is, "Referring to my sample table
and data, how can I calculate the FIFO cost for the outgoing items in rows
3,6 and 7?" Please ignore Rows 8 and 9 from the sample above.

Here is my DDL and Sample Data, updated and with reformatted comments for
easier use:

CREATE TABLE [dbo].[ItemTranx] (
[ID] [int] NOT NULL , /*this is an identity col but I removed it for this
demo so i can specify the row values*/
[ItemID] [int] NOT NULL , /*FK to Items table (for this sample they are
all the same item type) */
[TransactionType] [smallint] NOT
NULL, /* 1=Received(+),2=Adjustment(+/-),3=Sold(-) */
[Quantity] [decimal](18, 2) NOT NULL, /* positive for items received,
negative for outgoing */
[AmountPerUnit] [decimal](18, 2) NULL, /* actual cost for incoming items,
FIFO for outgoing */
[Date] [smalldatetime] NOT NULL,
[Comment] [nvarchar] (200) /* just for clarity with this example */
) ON [PRIMARY]

INSERT INTO [dbo].[ItemTranx] VALUES (1,1,1,50,12,'01-JAN-2007','+50 @ $12')
INSERT INTO [dbo].[ItemTranx] VALUES (2,1,2,100,10,'01-JAN-2007','+100 @
$10')
-- How to find FIFO cost (expecting 11.33) for AmountPerUnit in this line?:
INSERT INTO [dbo].[ItemTranx] VALUES (3,1,2,-75,11.33,'01-JAN-2007','-75 @
$11.33 ((50*12)+(25*10))/75')
INSERT INTO [dbo].[ItemTranx] VALUES (4,1,1,100,14,'01-JAN-2007','+100 @
$14')
INSERT INTO [dbo].[ItemTranx] VALUES (5,1,1,100,16,'01-JAN-2007','+100 @
$16')
-- How to find FIFO cost (expecting 11.60) for AmountPerUnit in this line?:
INSERT INTO [dbo].[ItemTranx] VALUES (6,1,3,-125,11.60,'01-JAN-2007','-125 @
$11.60 ((75*10)+(50*14))/125')
-- How to find FIFO cost (expecting 15.20) for AmountPerUnit in this line?:
INSERT INTO [dbo].[ItemTranx] VALUES (7,1,3,-125,15.20,'01-JAN-2007', '-125
@ $15.20 ((50*14)+(75*16))/125')

Tom Cooper

3/26/2007 7:47:00 PM

Hi Monty,

Here is a trigger that I think does most of what you want. It has some
limitations. It doesn't have much error checking and you should add that
for a production system. It needs a new column in your table named
QtyRemaining which has the remaining quantity available to be shipped for
each reciept. Note that a normal form purist will object to this since it
is a value that could be calculated from other data in the table. Also, the
trigger enforces that only one row is inserted into the table at a time and
that the inventory cannot go negative. Finally, it assumes that all of the
inventory transactions are ordered by ID, not by the Date and that ID is
unique. You will have to decide if these are "features" or "bugs". But,
hopefully, it gives you a starting place.

CREATE TRIGGER trItemTranx ON [dbo].[ItemTranx]
FOR INSERT AS
DECLARE @ID int,
@ItemID int,
@Quantity int,
@QtyRemaining int,
@FirstAvailableID int,
@LastRequiredID int,
@AmountPerUnit decimal(18,2)
-- Only one row can be inserted at a time
IF @@RowCount <> 1
BEGIN
ROLLBACK
RAISERROR ('Only 1 row may be inserted into ItemTranx at a time', 16, 1)
RETURN
END
-- Get data from inserted row
SELECT @ID = ID, @ItemID = ItemID, @Quantity = Quantity
FROM INSERTED
-- update QtyRemaining column
UPDATE [dbo].[ItemTranx] SET QtyRemaining =
CASE WHEN @Quantity > 0 THEN @Quantity ELSE 0 END
WHERE [dbo].[ItemTranx].ID = @ID
IF @Quantity < 0
-- Compute cost for sales/negative adjustments
BEGIN
SET @Quantity = Abs(@Quantity)
-- Find first and last reciept needed to satisy this amount
SELECT @FirstAvailableID = Min(t.ID)
FROM [dbo].[ItemTranx] t
WHERE t.ItemID = @ItemID AND t.QtyRemaining > 0
SELECT @LastRequiredID = Min(t.ID)
FROM [dbo].[ItemTranx] t
WHERE t.ItemID = @ItemID AND t.ID >= @FirstAvailableID
AND @Quantity <= (Select Sum (t1.QtyRemaining)
FROM [dbo].[ItemTranx] t1
WHERE t1.ItemID = @ItemID AND t1.ID >= @FirstAvailableID
AND t1.ID <= t.ID)
-- If can't find a last row, there is not enough inventory for this request
IF @LastRequiredID IS NULL
BEGIN
ROLLBACK
RAISERROR ('Inventory cant go negative', 16, 1)
RETURN
END
-- Determine quantity that will be remaining from last reciept used for this
sale
SELECT @QtyRemaining = Sum (t.QtyRemaining) - @Quantity
FROM [dbo].[ItemTranx] t
WHERE t.ItemID = @ItemID AND t.ID BETWEEN @FirstAvailableID AND
@LastRequiredID
-- Determine cost for this sale
SELECT @AmountPerUnit =
(Sum ((CASE WHEN t.ID = @LastRequiredID THEN t.QtyRemaining -
@QtyRemaining ELSE t.QtyRemaining END)
* t.AmountPerUnit)) / @Quantity
FROM [dbo].[ItemTranx] t
WHERE t.ItemID = @ItemID AND t.ID BETWEEN @FirstAvailableID AND
@LastRequiredID
-- Update qtyremaining for all reciepts used for this sale
-- Note that all but last row must have 0 remaining
UPDATE t SET QtyRemaining =
CASE WHEN t.ID = @LastRequiredID THEN @QtyRemaining ELSE 0 END
FROM [dbo].[ItemTranx] t
WHERE t.ItemID = @ItemID AND t.ID BETWEEN @FirstAvailableID AND
@LastRequiredID
-- Update cost for this sale
UPDATE t SET t.AmountPerUnit = @AmountPerUnit
FROM [dbo].[ItemTranx] t
WHERE t.ID = @ID
END
go

Good luck,
Tom

"Monty" <monty@community.nospam> wrote in message
news:%23xZurs8bHHA.2088@TK2MSFTNGP05.phx.gbl...
> Just to simplify this, ignore the question about the negative inventory
> for now. I think I've found a relatively elegant solution to prevent that
> situation from happening. So, my question is, "Referring to my sample
> table and data, how can I calculate the FIFO cost for the outgoing items
> in rows 3,6 and 7?" Please ignore Rows 8 and 9 from the sample above.
>
> Here is my DDL and Sample Data, updated and with reformatted comments for
> easier use:
>
> CREATE TABLE [dbo].[ItemTranx] (
> [ID] [int] NOT NULL , /*this is an identity col but I removed it for this
> demo so i can specify the row values*/
> [ItemID] [int] NOT NULL , /*FK to Items table (for this sample they are
> all the same item type) */
> [TransactionType] [smallint] NOT
> NULL, /* 1=Received(+),2=Adjustment(+/-),3=Sold(-) */
> [Quantity] [decimal](18, 2) NOT NULL, /* positive for items received,
> negative for outgoing */
> [AmountPerUnit] [decimal](18, 2) NULL, /* actual cost for incoming items,
> FIFO for outgoing */
> [Date] [smalldatetime] NOT NULL,
> [Comment] [nvarchar] (200) /* just for clarity with this example */
> ) ON [PRIMARY]
>
> INSERT INTO [dbo].[ItemTranx] VALUES (1,1,1,50,12,'01-JAN-2007','+50 @
> $12')
> INSERT INTO [dbo].[ItemTranx] VALUES (2,1,2,100,10,'01-JAN-2007','+100 @
> $10')
> -- How to find FIFO cost (expecting 11.33) for AmountPerUnit in this
> line?:
> INSERT INTO [dbo].[ItemTranx] VALUES (3,1,2,-75,11.33,'01-JAN-2007','-75 @
> $11.33 ((50*12)+(25*10))/75')
> INSERT INTO [dbo].[ItemTranx] VALUES (4,1,1,100,14,'01-JAN-2007','+100 @
> $14')
> INSERT INTO [dbo].[ItemTranx] VALUES (5,1,1,100,16,'01-JAN-2007','+100 @
> $16')
> -- How to find FIFO cost (expecting 11.60) for AmountPerUnit in this
> line?:
> INSERT INTO [dbo].[ItemTranx] VALUES (6,1,3,-125,11.60,'01-JAN-2007','-125
> @
> $11.60 ((75*10)+(50*14))/125')
> -- How to find FIFO cost (expecting 15.20) for AmountPerUnit in this
> line?:
> INSERT INTO [dbo].[ItemTranx] VALUES (7,1,3,-125,15.20,'01-JAN-2007',
> '-125
> @ $15.20 ((50*14)+(75*16))/125')
>
>
>

Monty

3/26/2007 10:11:00 PM

Hi Tom,

First off, thank you, I appreciate your generosity with your code.

For some reason I have an aversion to triggers and also computed columns, so
I was hoping to be able to do it with a view and a stored procedure, but
maybe I need to rethink my position. I will give it a try.

Thanks again.

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:%23eau299bHHA.1148@TK2MSFTNGP02.phx.gbl...
> Hi Monty,
>
> Here is a trigger that I think does most of what you want. It has some
> limitations. It doesn't have much error checking and you should add that
> for a production system. It needs a new column in your table named
> QtyRemaining which has the remaining quantity available to be shipped for
> each reciept. Note that a normal form purist will object to this since it
> is a value that could be calculated from other data in the table. Also,
> the trigger enforces that only one row is inserted into the table at a
> time and that the inventory cannot go negative. Finally, it assumes that
> all of the inventory transactions are ordered by ID, not by the Date and
> that ID is unique. You will have to decide if these are "features" or
> "bugs". But, hopefully, it gives you a starting place.
>
> CREATE TRIGGER trItemTranx ON [dbo].[ItemTranx]
> FOR INSERT AS
> DECLARE @ID int,
> @ItemID int,
> @Quantity int,
> @QtyRemaining int,
> @FirstAvailableID int,
> @LastRequiredID int,
> @AmountPerUnit decimal(18,2)
> -- Only one row can be inserted at a time
> IF @@RowCount <> 1
> BEGIN
> ROLLBACK
> RAISERROR ('Only 1 row may be inserted into ItemTranx at a time', 16, 1)
> RETURN
> END
> -- Get data from inserted row
> SELECT @ID = ID, @ItemID = ItemID, @Quantity = Quantity
> FROM INSERTED
> -- update QtyRemaining column
> UPDATE [dbo].[ItemTranx] SET QtyRemaining =
> CASE WHEN @Quantity > 0 THEN @Quantity ELSE 0 END
> WHERE [dbo].[ItemTranx].ID = @ID
> IF @Quantity < 0
> -- Compute cost for sales/negative adjustments
> BEGIN
> SET @Quantity = Abs(@Quantity)
> -- Find first and last reciept needed to satisy this amount
> SELECT @FirstAvailableID = Min(t.ID)
> FROM [dbo].[ItemTranx] t
> WHERE t.ItemID = @ItemID AND t.QtyRemaining > 0
> SELECT @LastRequiredID = Min(t.ID)
> FROM [dbo].[ItemTranx] t
> WHERE t.ItemID = @ItemID AND t.ID >= @FirstAvailableID
> AND @Quantity <= (Select Sum (t1.QtyRemaining)
> FROM [dbo].[ItemTranx] t1
> WHERE t1.ItemID = @ItemID AND t1.ID >= @FirstAvailableID
> AND t1.ID <= t.ID)
> -- If can't find a last row, there is not enough inventory for this
> request
> IF @LastRequiredID IS NULL
> BEGIN
> ROLLBACK
> RAISERROR ('Inventory cant go negative', 16, 1)
> RETURN
> END
> -- Determine quantity that will be remaining from last reciept used for
> this sale
> SELECT @QtyRemaining = Sum (t.QtyRemaining) - @Quantity
> FROM [dbo].[ItemTranx] t
> WHERE t.ItemID = @ItemID AND t.ID BETWEEN @FirstAvailableID AND
> @LastRequiredID
> -- Determine cost for this sale
> SELECT @AmountPerUnit =
> (Sum ((CASE WHEN t.ID = @LastRequiredID THEN t.QtyRemaining -
> @QtyRemaining ELSE t.QtyRemaining END)
> * t.AmountPerUnit)) / @Quantity
> FROM [dbo].[ItemTranx] t
> WHERE t.ItemID = @ItemID AND t.ID BETWEEN @FirstAvailableID AND
> @LastRequiredID
> -- Update qtyremaining for all reciepts used for this sale
> -- Note that all but last row must have 0 remaining
> UPDATE t SET QtyRemaining =
> CASE WHEN t.ID = @LastRequiredID THEN @QtyRemaining ELSE 0 END
> FROM [dbo].[ItemTranx] t
> WHERE t.ItemID = @ItemID AND t.ID BETWEEN @FirstAvailableID AND
> @LastRequiredID
> -- Update cost for this sale
> UPDATE t SET t.AmountPerUnit = @AmountPerUnit
> FROM [dbo].[ItemTranx] t
> WHERE t.ID = @ID
> END
> go
>
> Good luck,
> Tom
>
> "Monty" <monty@community.nospam> wrote in message
> news:%23xZurs8bHHA.2088@TK2MSFTNGP05.phx.gbl...
>> Just to simplify this, ignore the question about the negative inventory
>> for now. I think I've found a relatively elegant solution to prevent that
>> situation from happening. So, my question is, "Referring to my sample
>> table and data, how can I calculate the FIFO cost for the outgoing items
>> in rows 3,6 and 7?" Please ignore Rows 8 and 9 from the sample above.
>>
>> Here is my DDL and Sample Data, updated and with reformatted comments for
>> easier use:
>>
>> CREATE TABLE [dbo].[ItemTranx] (
>> [ID] [int] NOT NULL , /*this is an identity col but I removed it for
>> this
>> demo so i can specify the row values*/
>> [ItemID] [int] NOT NULL , /*FK to Items table (for this sample they are
>> all the same item type) */
>> [TransactionType] [smallint] NOT
>> NULL, /* 1=Received(+),2=Adjustment(+/-),3=Sold(-) */
>> [Quantity] [decimal](18, 2) NOT NULL, /* positive for items received,
>> negative for outgoing */
>> [AmountPerUnit] [decimal](18, 2) NULL, /* actual cost for incoming items,
>> FIFO for outgoing */
>> [Date] [smalldatetime] NOT NULL,
>> [Comment] [nvarchar] (200) /* just for clarity with this example */
>> ) ON [PRIMARY]
>>
>> INSERT INTO [dbo].[ItemTranx] VALUES (1,1,1,50,12,'01-JAN-2007','+50 @
>> $12')
>> INSERT INTO [dbo].[ItemTranx] VALUES (2,1,2,100,10,'01-JAN-2007','+100 @
>> $10')
>> -- How to find FIFO cost (expecting 11.33) for AmountPerUnit in this
>> line?:
>> INSERT INTO [dbo].[ItemTranx] VALUES (3,1,2,-75,11.33,'01-JAN-2007','-75
>> @
>> $11.33 ((50*12)+(25*10))/75')
>> INSERT INTO [dbo].[ItemTranx] VALUES (4,1,1,100,14,'01-JAN-2007','+100 @
>> $14')
>> INSERT INTO [dbo].[ItemTranx] VALUES (5,1,1,100,16,'01-JAN-2007','+100 @
>> $16')
>> -- How to find FIFO cost (expecting 11.60) for AmountPerUnit in this
>> line?:
>> INSERT INTO [dbo].[ItemTranx] VALUES
>> (6,1,3,-125,11.60,'01-JAN-2007','-125 @
>> $11.60 ((75*10)+(50*14))/125')
>> -- How to find FIFO cost (expecting 15.20) for AmountPerUnit in this
>> line?:
>> INSERT INTO [dbo].[ItemTranx] VALUES (7,1,3,-125,15.20,'01-JAN-2007',
>> '-125
>> @ $15.20 ((50*14)+(75*16))/125')
>>
>>
>>
>
>

changliw

3/27/2007 7:45:00 AM

Hi, Monty,
Is it possible to simplify your business rules? It seemed very complicated
and not same as that article you mentioned.

Assuming the following situation:
1 1 1 50.00 12.00 2007-01-01 00:00:00 +50 @ $12
2 1 2 100.00 10.00 2007-01-01 00:00:00 +100 @ $10
3 1 2 -75.00 11.33 2007-01-01 00:00:00 -75 @ $11.33 ((50*12)+(25*10))/75
4 1 1 100.00 14.00 2007-01-01 00:00:00 +100 @ $14
5 1 1 100.00 16.00 2007-01-01 00:00:00 +100 @ $16
6 1 1 50 17.00 2007-01-01 00:00:00 +50 @ $17
7 1 1 20 13.00 2007-01-01 00:00:00 +20 @ $ 13.00
8 1 3 -330
............................................................................
..............
The row 8 amountperunit field should be calculated by
(75*10+100*14+100*16+50*17+5*13)/330.

Also, the complex of your situation is that it does not strictly follow
FIFO principle since multi-re-entery is possible.
For example:
1 ... 100 12 ....
2 ... 200 10 .....
3 ... -30 12 ....
4 ... -30 12 ....
5 ... -30 12 ....
.................

A simple query statement cannot resolve such a complex business logic. If
you do not want to use trigger, I am afraid that you may consider to use
cursor and stored procedure to implement the business logic.

If you have any other questions or concerns, please feel free to let us
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Monty

3/28/2007 5:15:00 AM

Hi Charles,

> Is it possible to simplify your business rules? It seemed very complicated
> and not same as that article you mentioned.

Yes, my business rules are somewhat flexible, what do you suggest? I do need
to account for incoming inventory, adjustments of inventory, and outgoing
inventory. Correct, it is not exactly the same as the article which is why
I'm having trouble applying Joe's logic to mine.

> The row 8 amountperunit field should be calculated by
> (75*10+100*14+100*16+50*17+5*13)/330.
Correct, what is the best way to do it in SQL?

> Also, the complex of your situation is that it does not strictly follow
> FIFO principle since multi-re-entery is possible.

Not sure I followed you here. I googled "FIFO" and "multiple re-entry" and
"multi re-entry" but didn't come up with much. Do you have a link that
describes this? Is it possible you're thinking about something other than
FIFO ~Cost~ calculations?

> A simple query statement cannot resolve such a complex business logic. If
> you do not want to use trigger, I am afraid that you may consider to use
> cursor and stored procedure to implement the business logic.

Yes, I was thinking a query and a view, but I'm willing to listen to other
ideas.

Thanks Charles,

Monty

"Charles Wang[MSFT]" <changliw@online.microsoft.com> wrote in message
news:rOrCIPEcHHA.1608@TK2MSFTNGHUB02.phx.gbl...
> Hi, Monty,
> Is it possible to simplify your business rules? It seemed very complicated
> and not same as that article you mentioned.
>
> Assuming the following situation:
> 1 1 1 50.00 12.00 2007-01-01 00:00:00 +50 @ $12
> 2 1 2 100.00 10.00 2007-01-01 00:00:00 +100 @ $10
> 3 1 2 -75.00 11.33 2007-01-01 00:00:00 -75 @ $11.33 ((50*12)+(25*10))/75
> 4 1 1 100.00 14.00 2007-01-01 00:00:00 +100 @ $14
> 5 1 1 100.00 16.00 2007-01-01 00:00:00 +100 @ $16
> 6 1 1 50 17.00 2007-01-01 00:00:00 +50 @ $17
> 7 1 1 20 13.00 2007-01-01 00:00:00 +20 @ $ 13.00
> 8 1 3 -330
> ...........................................................................
> .............
> The row 8 amountperunit field should be calculated by
> (75*10+100*14+100*16+50*17+5*13)/330.
>
> Also, the complex of your situation is that it does not strictly follow
> FIFO principle since multi-re-entery is possible.
> For example:
> 1 ... 100 12 ....
> 2 ... 200 10 .....
> 3 ... -30 12 ....
> 4 ... -30 12 ....
> 5 ... -30 12 ....
> ................
>
> A simple query statement cannot resolve such a complex business logic. If
> you do not want to use trigger, I am afraid that you may consider to use
> cursor and stored procedure to implement the business logic.
>
> If you have any other questions or concerns, please feel free to let us
> know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
> ications
>
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/de....
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================
>
>
>

changliw

3/29/2007 10:32:00 AM

Hi, Monty,
Thanks for your response.

> Yes, my business rules are somewhat flexible, what do you suggest?
I have a question:
Why did you need to automatically cacluate the cost per unit of outgoing
items?

>Not sure I followed you here. I googled "FIFO" and "multiple re-entry" and
>"multi re-entry" but didn't come up with much. Do you have a link that
>describes this? Is it possible you're thinking about something other than
>FIFO ~Cost~ calculations?

The multi-re-entry that I said means that one AmountPerUnit may be used
multiple times, that is why I do not think that this is a typical FIFO
scenario. In a typical FIFO, one in item corresponds to one out item. For
example, at first, if I enqueue 10 objects as an item, then the 10 objects
will be dequeued together as an item and after that the item does not exist.

Your scenario is more complex and cannot be simply summarized by FIFO
though the direction seems to be. Let us summarize your business logic:
When an outgoing record is added, it should be calculated via:
1. Check the quantity of current inbox items to see if there are enough
items for outgoing;
2. If the current inbox items quantity value > outgoing items quantity
value, check to find the appropriate header row for calculating. The
algorithm may be from the first row to the last or conversely.
3. Find the header row, compare the quantity values and calculate the
difference value if the ougoing item quantity value is larger than the
header item quantity value, and then compare the difference value with the
next item until the difference value is less than the inbox item quantity
value.

It is very hard for a simple query to implement such a complex business
logic. You may use a stored procedure to implement it. Appreciate your
understanding on this.

If you have any other questions or concerns, please feel free to let me
know.

Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Monty

3/29/2007 2:00:00 PM

> Why did you need to automatically cacluate the cost per unit of outgoing
> items?

There are several ways to value inventory and cost of goods sold, and FIFO
is one of them. Businesses need to know how much the goods that they are
selling them actually cost them. If by flexibility in my business
requirements you meant not offering a FIFO calculation at all, then no, it's
not that flexible.

> The multi-re-entry that I said means that one AmountPerUnit may be used
> multiple times, that is why I do not think that this is a typical FIFO
> scenario.

I'm not sure how my system violates the multiple re-entry guideline. Given
my table, let's assume we create a view that tells us how much of each item
is left in inventory. So, now we have an outgoing transaction for 50 items,
we look at the view and find out our current quantity on hand is 1000 items.
Using FIFO, we find the average cost of the first 50 items out of that 1000
that we currently have on hand. Now get another outgoing transaction for 25
items.... we look at our view and find out we have 950 items on hand, so we
look at our transaction history to get the cost of the first 25 items out of
that 950 that we have on hand. We're using the first of the 950 instead of
out of 100 because those 50 that already went out in the last transaction
have already been counted and cannot be used again. So I'm not sure how that
violates the FIFO guidelines.

So, I'm just looking for a way, in SQL, to get the average AmountPerUnit of
those first ## items. Sorry if I wasn't clear on that initially.

"Charles Wang[MSFT]" <changliw@online.microsoft.com> wrote in message
news:F5ndI2ecHHA.1192@TK2MSFTNGHUB02.phx.gbl...
> Hi, Monty,
> Thanks for your response.
>
>> Yes, my business rules are somewhat flexible, what do you suggest?
> I have a question:
> Why did you need to automatically cacluate the cost per unit of outgoing
> items?
>
>>Not sure I followed you here. I googled "FIFO" and "multiple re-entry" and
>>"multi re-entry" but didn't come up with much. Do you have a link that
>>describes this? Is it possible you're thinking about something other than
>>FIFO ~Cost~ calculations?
>
> The multi-re-entry that I said means that one AmountPerUnit may be used
> multiple times, that is why I do not think that this is a typical FIFO
> scenario. In a typical FIFO, one in item corresponds to one out item. For
> example, at first, if I enqueue 10 objects as an item, then the 10 objects
> will be dequeued together as an item and after that the item does not
> exist.
>
> Your scenario is more complex and cannot be simply summarized by FIFO
> though the direction seems to be. Let us summarize your business logic:
> When an outgoing record is added, it should be calculated via:
> 1. Check the quantity of current inbox items to see if there are enough
> items for outgoing;
> 2. If the current inbox items quantity value > outgoing items quantity
> value, check to find the appropriate header row for calculating. The
> algorithm may be from the first row to the last or conversely.
> 3. Find the header row, compare the quantity values and calculate the
> difference value if the ougoing item quantity value is larger than the
> header item quantity value, and then compare the difference value with the
> next item until the difference value is less than the inbox item quantity
> value.
>
> It is very hard for a simple query to implement such a complex business
> logic. You may use a stored procedure to implement it. Appreciate your
> understanding on this.
>
> If you have any other questions or concerns, please feel free to let me
> know.
>
> Have a good day!
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
> ications
>
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/de....
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================
>
>
>

changliw

3/30/2007 12:13:00 PM

Hi, Monty,
Thanks for your detailed response.

I think I understand your meaning. From your description, we can see that
this is a FIFO transaction scenario, not a FIFO item scenario. In other
words, the FIFO transaction is not atomic. That is why I do not think that
it is a typical FIFO scenario. I understand your expectation, but due to
its complex I also appreciate your understanding that this may not be a
problem that can be resolved by one or several simple SQL statements.

You may consider to encapsulate your business logic into a stored procedure
or application or in a trigger as Tom mentioned. Anyway, let us wait to see
if any other experts have some good idea on this issue.

NOTE: Our managed newsgroup is focused on break fix issues that are neither
urgent nor complex. If the issue is urgent to your business, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.mic.../default.aspx?scid=fh;EN-US;PH...

If you are outside the US please see http://support.mic... for
regional support phone numbers.

If you have any other questions or concerns, please feel free to let us
know.
Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

microsoft.public.sqlserver.programming

FIFO Cost Calculation

Monty

Monty

Tom Cooper

Monty

changliw

Monty

changliw

Monty

changliw

x Login to ForumsZone