Summation of FIFO values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Summation of FIFO values

Author

Topic

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-04-08 : 19:57:17

Hello All,

I'm dealing with a scenario as below where I need to evaluate the total cost of any given amount of a specific items (Item Number) based on FIFO by Lot Number with variable costs. IOW If I remove 50 units of item 102 as apposed to 75, 100, and so on, what will be the total cost? I can't figure a simple way to do it with a single Select statement.

CREATE TABLE Stock(LotNumber INT, ItemNumber INT, units int, Cost money)

INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(1, 101, 100, 23.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(2, 102, 45, 12.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(3, 101, 65, 21.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(4, 103, 103, 17.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(5, 101, 180, 28.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(6, 102, 25, 16.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(7, 102, 175, 12.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(8, 102, 180, 15.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(9, 102, 200, 16.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(10, 105, 75, 20.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(11, 106, 180, 20.00)
INSERT INTO Stock (LotNumber, ItemNumber, units, Cost) VALUES(12, 101, 190, 22.00)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-11 : 07:17:29

Not sure I understand correctly and I have no clue whatsoever what "FIFO" or "IOW" means but heres a shot in the dark:

SELECT ItemNumber, SUM(units * Cost) AS TotalCost FROM Stock GROUP BY ItemNumber

or

SELECT SUM(units * Cost) AS TotalCost FROM Stock

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 09:27:22

FIFO, LIFO, FOLI, whatever...

The order of data in a database has no meaning....should be a banner on the home page..

First in Forst Out btw...

IOW?

I got Isle of Wright when googled...

If you want to know WHEN the data was added...you need a timestamp...

ummm datetime column...damn come on Yukon....

Brett

8-)

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-04-12 : 11:56:17

Sorry guys, on the lousy explanation.

It's costing on First In First Out basis, considering LotNumber as the sequence.

Example:

100 units of 102 would be:

45 * 12.00 = 540.00 (lot 2)
25 * 16.00 = 400.00 (lot 6)
30 * 12.00 = 360.00 (lot 7)
--------------------
1300.00 total / 13.00 per unit

300 units of 102 would be:

45 * 12.00 = 540.00 (lot 2)
25 * 16.00 = 400.00 (lot 6)
175 * 12.00 = 2100.00 (lot 7)
55 * 15.00 = 825.00 (lot 8)
-------------------
3865.00 total / 12.88 per unit

This example would easily be done client side, but it's is not the actual project I'm doing. The principle is the same.

(IOW - In Other Words)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 12:10:38

I'm still missing it....300 Units?

Of 102?

Brett

8-)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-12 : 13:48:04

You'll need a tally table of numbers but here is an attempt:

CREATE FUNCTION dbo.GetStockItems(@ItemNum int)
RETURNS @OrderTable table
	(
	OrderID int IDENTITY(1,1)
	, LotNumber int
	, Cost money
	)
AS
BEGIN
	INSERT	@OrderTable
	SELECT	b.LotNumber, b.Cost
	FROM	Numbers a JOIN stock b
		ON a.Number <= b.Units
	WHERE	ItemNumber = @ItemNum
	ORDER BY b.LotNumber ASC ---FIFO 
RETURN
END


DECLARE @OrderCount int, @ItemNumber1 int

SET @OrderCount = 100
SET @ItemNumber1 = 102

SELECT	@ItemNumber1, COUNT(OrderID), SUM(Cost)
FROM	dbo.GetStockItems(@ItemNumber1)
WHERE	OrderID <= @OrderCount

GO
DROP FUNCTION dbo.GetStockItems

Results in:

Item Number Order Count Order Amount          
----------- ----------- --------------------- 
102         100         1300.0000

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-04-12 : 14:47:37

drymchaser...you're the dawg dude! Worked like a charm.

My first attempt was with a "While loop and Select Top 1 query" but you could measure the performance with a sundial.

I think I've been spending too much time in the trenches. I completely forgot a function could return a table and that tally table, what a stroke of genus. I have a thousand uses for that one.

...just don't be telling me it's common knowledge, because I'd be feeling a little stupid right now.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-12 : 14:51:37

Lots of uses for "tally" or "number" tables have been shown here. Do a search on that for others. They are nice tools for the toolbox.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources