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
Go to Top of Page

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-04 : 03:32:53
Wow. Let me read your code and try it out. Thanks
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

hayashiryo
Starting Member

46 Posts

Posted - 2014-04-16 : 22:46:07
Hey stepson. I've got it working! Thanks a million!!
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-17 : 01:00:32
With welcome!


sabinWeb MCP
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

hayashiryo
Starting Member

46 Posts

Posted - 2015-03-01 : 23:34:21
Hi Stepson, I managed to get it working! Thanks a million!
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
    Next Page

- Advertisement -