Stock Valuation using FILO - 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 2008 Forums

Transact-SQL (2008)

Stock Valuation using FILO

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-04 : 00:27:28

Hi all,

I'm trying to do a stock valuation report. And I need some help or ideas on how to achieve this report.

My system is an inventory system. During financial year end, I need to generate a stock valuation report. Ie. I need to calculate the value of my stocks.

How to determine stock valuation using FIFO logic
(1) We bought 10pens at $5.
Stock valuation will be 10*$5 = $50

(2) We bought another 5pens, but this time at $6.
Stock valuation will be (10*$5)+(5*$6) = $80

(3) We sold 7 pens
Stock valuation will be ((10-7)*$5)+(5*$6) = $45

(4) We sold another 4 pens
Stock valuation will be ((10-10)*$5)+((5-1)*$6) = $24

So you see, to determine stock valuation using First-In-First-Out (FIFO), we need to determine the stock value according to it's first purchase price unless that initial stock have been sold out . At point (4) the initial 10pens have been sold off. So to determine the stock valuation we look at the next purchase price only and so on and so forth. (Sorry if I'm confusing you.)

I can generate the following 2 tables from my existing database


Type		Item	Date		Qty	Purchase Price
Purchase	Pen	04-Jun-13	10	 $5 
Purchase	Pen	10-Sep-13	5	 $6 
Purchase	Pen	08-Oct-13	20	 $4 
				
Type	Item	Date		Qty
Sale	Pen	07-Jul-13	7	
Sale	Pen	08-Sep-13	8	
Sale	Pen	28-Sep-13	15	
Sale	Pen	11-Nov-13	5

I'm stuck trying to calculate the final stock valuation using FILO logic.

Thanks in advance for any ideas or guidance.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-04 : 03:25:43

[code]

declare @varTable as Table
( [type] varchar(30)
,[Item] varchar(30)
,[Date] Datetime
,[Qty] int
,[Price] float
)
declare @sItem varchar(30),@sDate datetime,@sQty float,@sPrice float, @i int,@rowcount int
declare @pItem varchar(30),@pDate datetime,@pQty float,@pPrice float

if Object_ID('tempdb.dbo.#stocks') is not null
begin
drop table tempdb.dbo.#stocks
end
create table #stocks
(
[Item] varchar(30)
,[Date] Datetime
,[Qty] int
,[Price] float
)

insert into #stocks([Item],[Date],[Qty],[Price])
select item,date,qty,price
from Purchase
order by date

--select * from #stocks

labelSale:
select top 1
@sItem=item
,@sDate=date
,@sQty=qty
from Sale
where date>coalesce(@sDate,'1900-01-01')

set @rowcount=@@ROWCOUNT

print 'Sale Item: '+@sItem + ' ,date: ' + convert(varchar(30),@sDate,112) + ' ,Qty: ' + convert(varchar(30),@sQty)

while @rowcount>0
begin
labelPurchase:
select top 1
@pItem=Item
,@pDate=date
,@pQty=Qty
,@pPrice=Price
from #stocks
where qty>0
Order by Date

set @rowcount=@@ROWCOUNT
print 'Purchase Item: '+@pItem + ' ,date: ' + convert(varchar(30),@pDate,112) + ' ,Qty: ' + convert(varchar(30),@pQty)+ ' ,Price: ' + convert(varchar(30),@pPrice)

if (@pQty>=@sQty)
begin
insert into @varTable([type],[Item],[Date],[Qty],[Price])
values('Sale',@sItem,@sDate,@sQty,@pPrice)

set @pQty=@pQty-@sQty

update top (1) #stocks
set qty=@pQty
where qty>0

goto labelSale
end
else
begin
insert into @varTable([type],[Item],[Date],[Qty],[Price])
values('Sale',@sItem,@sDate,@pQty,@pPrice)

set @sQty=@sQty-@pQty

update top (1) #stocks
set qty=0
where qty>0

goto labelPurchase
end

end

select * from @varTable
drop table tempDB..#stocks
[/code]

output
[code]
type Item Date Qty Price
Sale Pen 2013-07-07 00:00:00.000 7 5
Sale Pen 2013-09-12 00:00:00.000 3 5
Sale Pen 2013-09-12 00:00:00.000 5 6
Sale Pen 2013-09-28 00:00:00.000 15 4
Sale Pen 2013-11-11 00:00:00.000 5 4
[/code]

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-04 : 03:32:53

Wow. Let me read your code and try it out. Thanks

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-07 : 05:52:38

I've tried your code. But there's one problem.

I have a lot of products in my database. And also a lot of purchase and sales record.

Using your code, when I specify 1 product code, mssql was able to retrieve the records for me.

However, when I don't specify a product code (ie to retrieve for all stockable products), the system seems to time out or hang.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-07 : 06:18:27

-Add index/s on Item and Date
-#stocks could be a normal table , with index on it

also you can tun this script
I mean


select top 1 
			@pItem=Item
			,@pDate=date
			,@pQty=Qty
			,@pPrice=Price
		from #stocks
		where qty>0
[code]

could be replace something base on ID
[code]
select top 1 
			@pItem=Item
			,@pDate=date
			,@pQty=Qty
			,@pPrice=Price
                        ,@id=ID
		from #stocks
		where ID>@oldID

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-07 : 06:19:59

also you can change "insert into @varTable"
with an insert into a normal Table

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-07 : 23:10:33

Hi Stepson. I think I have been approaching this problem from a wrong angle.

After consulting a accountant, a better approach maybe to take the Quantity-On-Hand (QOH) and work backwards on all the purchase orders to determine valuation.

Inventory Listing
Item		QOH
Pen		150
Ruler		50

Purchase Orders
OrderID		OrderDate	Item	Qty	UnitPrice	Valuation
00004		2014-02-15	Pen	142	2.5000		355.0000
00003		2014-01-20	Pen	25	2.7500		 68.7500
00002		2014-01-20	Ruler	60	2.0000		120.0000
00001		2014-01-01	Ruler	100	1.8000		180.0000

Looking at the QOH from the first table, we have to allocate the QOH value to the purchase order starting from the latest record and deduct the amount from the subsequent purchase order so on and so forth. Please review the logic below


Logic
For each order, compare the QOH and Qty value
If (QOH > Qty)
	Set Order to Qty value
	QOH = QOH - Qty
Else	
	Set Order to QOH value

Example
Pen's QOH = 150

Order 00004
If (150 > 142)
	Order0004.Qty = 142
	QOH = 150 - 142 = 8
Else	
	NA

--No change required. The valuation column remains unchanged at 142*2.5 = 355

Order 00003
If (8 > 25)
	NA
Else
	Order0003.Qty = 8
Since Order0003.Qty is updated to 8, the valuation column will change to 8*2.75 = 22

Same logic for the Ruler item. In this case order 00002 Qty will be changed to 50 and Order 00003 to 0

Therefore the final result should be


OrderID		OrderDate	Item	Qty	UnitPrice	Valuation
00004		2014-02-15	Pen	142	2.5000		355.0000
00003		2014-01-20	Pen	8	2.7500		 22.0000
00002		2014-01-20	Ruler	50	2.0000		100.0000
00001		2014-01-01	Ruler	0	1.8000		  0.0000

Apologies for not coming up with this solution earlier. Any ideas or suggestion to come up with a SQL script for this is much appreciated.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-08 : 01:32:08

[code]

;with InventoryListing
AS
(select 'Pen' as Item,150 as QOH union all
select 'Ruler',50)
,PurchaseOrders
AS
(select '00004'as OrderID,'2014-02-15' as OrderDate,'Pen' as Item,142 as Qty,2.5000 as UnitPrice,355.0000 Valuation union all
select '00003','2014-01-20','Pen',25,2.7500,68.7500 union all
select '00002','2014-01-20','Ruler',60,2.0000,120.0000 union all
select '00001','2014-01-01','Ruler',100,1.8000,180.0000)

select

OrderID
,OrderDate
,PO.Item
,case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end as Qty
,UnitPrice
,[Valuation] = case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end
* UnitPrice
/*
PO.*
,IL.*

,[QtyStock]
,case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end as newQty*/
from
PurchaseOrders PO
INNER JOIN InventoryListing IL
ON PO.Item=IL.Item
OUTER APPLY
(
SELECT
sum(Qty) as [QtyStock]
FROM PurchaseOrders PO2
WHERE PO.Item=PO2.Item
and PO.OrderDate<=PO2.OrderDate
) New
ORDER BY PO.Item
,OrderDate DESC
[/code]

output:
[code]
OrderID OrderDate Item Qty UnitPrice Valuation
00004 2014-02-15 Pen 142 2.5000 355.0000
00003 2014-01-20 Pen 8 2.7500 22.0000
00002 2014-01-20 Ruler 50 2.0000 100.0000
00001 2014-01-01 Ruler 0 1.8000 0.0000
[/code]

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-16 : 22:46:07

Hey stepson. I've got it working! Thanks a million!!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-17 : 01:00:32

With welcome!

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2015-01-20 : 03:42:59

Hi guys,

This is a old thread. But recently I faced 1 issue with Stepson's solution

Please refer to my updated question posted on 04/07/2014

Some of our very old stocks may not have PurchaseOrder records. My database will only store the QOH value.
Note: The original purchase order records could have been purged as they are really old records.

In this case, I need the valuation to be $0.00

In Stepson's solution, because there is no PurchaseOrder records, the results are not shown at all.

Ex.

In the records, below, the Eraser products will not show because there is no purchase order records. How can I still include Eraser records as $0.00 value?


Inventory Listing
Item		QOH
Pen		150
Ruler		50
Erasers		10

Purchase Orders
OrderID		OrderDate	Item	Qty	UnitPrice	Valuation
00004		2014-02-15	Pen	142	2.5000		355.0000
00003		2014-01-20	Pen	25	2.7500		 68.7500
00002		2014-01-20	Ruler	60	2.0000		120.0000
00001		2014-01-01	Ruler	100	1.8000		180.0000

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-20 : 04:43:07

[code]
;with InventoryListing
AS
(select 'Pen' as Item,150 as QOH union all
select 'Ruler',50 UNION ALL
select 'Erasers',10)
,PurchaseOrders
AS
(select '00004'as OrderID,'2014-02-15' as OrderDate,'Pen' as Item,142 as Qty,2.5000 as UnitPrice,355.0000 Valuation union all
select '00003','2014-01-20','Pen',25,2.7500,68.7500 union all
select '00002','2014-01-20','Ruler',60,2.0000,120.0000 union all
select '00001','2014-01-01','Ruler',100,1.8000,180.0000)

select

OrderID
,OrderDate
,IL.Item
,case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end as Qty
,UnitPrice
,[Valuation] = case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end
* UnitPrice

from
InventoryListing IL
LEFT JOIN PurchaseOrders PO
ON PO.Item=IL.Item
OUTER APPLY
(
SELECT
sum(Qty) as [QtyStock]
FROM PurchaseOrders PO2
WHERE PO.Item=PO2.Item
and PO.OrderDate<=PO2.OrderDate
) New
ORDER BY PO.Item
,OrderDate DESC
[/code]

output:
[code]
OrderID OrderDate Item Qty UnitPrice Valuation
NULL NULL Erasers 0 NULL NULL
00004 2014-02-15 Pen 142 2.5000 355.0000
00003 2014-01-20 Pen 8 2.7500 22.0000
00002 2014-01-20 Ruler 50 2.0000 100.0000
00001 2014-01-01 Ruler 0 1.8000 0.0000
[/code]

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2015-01-20 : 04:58:26

Hmm. Buy my Eraser products qty should be 10. I need the UnitPrice and Valuation to be $0.00

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-20 : 06:01:27

[code]
;with InventoryListing
AS
(select 'Pen' as Item,150 as QOH union all
select 'Ruler',50 UNION ALL
select 'Erasers',10)
,PurchaseOrders
AS
(select '00004'as OrderID,'2014-02-15' as OrderDate,'Pen' as Item,142 as Qty,2.5000 as UnitPrice,355.0000 Valuation union all
select '00003','2014-01-20','Pen',25,2.7500,68.7500 union all
select '00002','2014-01-20','Ruler',60,2.0000,120.0000 union all
select '00001','2014-01-01','Ruler',100,1.8000,180.0000)

select

OrderID
,OrderDate
,IL.Item
,case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
when po.Item is null then IL.QOH
else 0 end as Qty
,UnitPrice = isnull(UnitPrice,0)
,[Valuation] = isnull(case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end
* UnitPrice,0)

from
InventoryListing IL
LEFT JOIN PurchaseOrders PO
ON PO.Item=IL.Item
OUTER APPLY
(
SELECT
sum(Qty) as [QtyStock]
FROM PurchaseOrders PO2
WHERE PO.Item=PO2.Item
and PO.OrderDate<=PO2.OrderDate
) New
ORDER BY PO.Item
,OrderDate DESC
[/code]

output:
[code]
OrderID OrderDate Item Qty UnitPrice Valuation
NULL NULL Erasers 10 0.0000 0.0000
00004 2014-02-15 Pen 142 2.5000 355.0000
00003 2014-01-20 Pen 8 2.7500 22.0000
00002 2014-01-20 Ruler 50 2.0000 100.0000
00001 2014-01-01 Ruler 0 1.8000 0.0000
[/code]

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2015-03-01 : 23:34:21

Hi Stepson, I managed to get it working! Thanks a million!

hayashiryo
Starting Member

46 Posts

Posted - 2015-03-18 : 00:36:53

Hi Stepson,

I have a bug that appeared based on your last solution. Actually, it isn't really a bug, but an anomaly in my database.

Some of my products QOH can be negative in value.

This happens when we sell products that we have 0 in stock.
(Ie We had 0 in stock but we still sell to our customers. We will back order and deliver to them later. But we need to key in our order first. Thus our QOH became negative in value)

In this case, how would you tweak your sql statement?


Inventory Listing
Item		QOH
Notepad		-10 (We sold 10 notepad when we had 0 stock. Thus -10)

Purchase Orders
OrderID		OrderDate	Item	Qty	UnitPrice	Valuation
00009		2014-01-25	Notepad	50	2.5000		125.0000
00008		2014-01-12	Notepad	100	2.7500		275.0000
00007		2014-01-2	Notepad	150	2.7500		412.5000

I need output to be


OrderID	OrderDate	Item	Qty	UnitPrice	Valuation
00009	2014-01-25	Notepad	-10	2.5000		0.0000
00008	2014-01-12	Notepad	0	2.7500		0.0000
00007	2014-01-2	Notepad	0	2.7500		0.0000

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 02:57:56

[code]

;with InventoryListing
AS
(select 'Notepad' as Item,-10 as QOH)
,PurchaseOrders
AS
(select '00009'as OrderID,'2014-01-15' as OrderDate,'Notepad' as Item,50 as Qty,2.5000 as UnitPrice,125.0000 Valuation union all
select '00008','2014-01-12','Notepad',100,2.7500,275.0000 union all
select '00007','2014-01-02','Notepad',150,2.7500,412.5000)

select

OrderID
,OrderDate
,IL.Item

,case when QOH>QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
when po.Item is null then IL.QOH
when QOH < 0 AND Qty >= QtyStock then IL.QOH
else 0 end as Qty

,UnitPrice = isnull(UnitPrice,0)
,[Valuation] = isnull(case when QOH > QtyStock then Qty
when QOH-QtyStock+Qty> 0 then QOH-QtyStock+Qty
else 0 end
* UnitPrice,0)

from
InventoryListing IL
LEFT JOIN PurchaseOrders PO
ON PO.Item=IL.Item
OUTER APPLY
(
SELECT
sum(Qty) as [QtyStock]
FROM PurchaseOrders PO2
WHERE PO.Item=PO2.Item
and PO.OrderDate<=PO2.OrderDate
) New
ORDER BY PO.Item
,OrderDate DESC
[/code]

and output:
[code]
OrderID OrderDate Item Qty UnitPrice Valuation
00009 2014-01-15 Notepad -10 2.5000 0.0000
00008 2014-01-12 Notepad 0 2.7500 0.0000
00007 2014-01-02 Notepad 0 2.7500 0.0000
[/code]

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2015-03-18 : 03:35:12

Nice. So simple!

One more question.

In the code below you are simply keying in test data. For me I replaced it by retrieving actual Purchase Order records from my DB.

These records need to be sorted in ASC order by Date. However, when I add a ORDER DATE ASC, it gives me an error. How would we solve this?

Your Code
---------


,PurchaseOrders
AS
	(select '00009'as OrderID,'2014-01-15' as OrderDate,'Notepad' as Item,50 as Qty,2.5000	as UnitPrice,125.0000 Valuation union all
	 select '00008','2014-01-12','Notepad',100,2.7500,275.0000 union all
	 select '00007','2014-01-02','Notepad',150,2.7500,412.5000)

My Code
-------


,PurchaseOrders
AS
	(
		SELECT PO.OrderID, PO.OrderDate, POD.Item, POD.Qty, POD.UnitPrice, POD.Qty*POD.UnitPrice As 'Valuation'
		FROM PurchaseOrderDetails POD INNER JOIN PurchaseOrders PO on POD.OrderID = PO.OrderID
		ORDER BY OrderDate ASC
	)

Error:
Msg 1033, Level 15, State 1, Line 138
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 03:56:33

just add Top on it


,PurchaseOrders
AS
	(
		SELECT TOP 100 PERCENTPO.OrderID, PO.OrderDate, POD.Item, POD.Qty, POD.UnitPrice, POD.Qty*POD.UnitPrice As 'Valuation'
		FROM PurchaseOrderDetails POD INNER JOIN PurchaseOrders PO on POD.OrderID = PO.OrderID
		ORDER BY OrderDate ASC
	)

sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2015-03-18 : 04:17:07

I read from this forum, that TOP 100PERCENT doesn't seem to guarantee sorting. Any thoughts on this?

http://stackoverflow.com/questions/985921/sql-error-with-order-by-in-subquery

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-18 : 14:25:13

You should add the ORDER by clause in your final (last) SELECT.

The output of CTE's (such as PurchaseOrders) is a (virtual) table, which by definition, has no order. The only reason to use ORDER BY clause in such cases would be if you wanted to select top N records or top N percent records. Even so, the output is not an ordered collection.

The final select returns the output to you as a cursor (i.e., it has an order to it). So you can add an order by clause to it and be guaranteed that the results you get would be ordered according to your order by clause.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources