inventory/stock valuation - 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

Development Tools

Other Development Tools

inventory/stock valuation

Author

Topic

folumike
Starting Member

24 Posts

Posted - 2012-05-20 : 15:24:30

I am developing an application with vb6 and mssql 2000. I need to get inventory/stock valuation. In the process, i need to calculate using array: Date,Inv#,Qty for val, Cost Price. Then get the average price. See below:
Declare i,jk,m
k = 0
i=1
Do while k<=TotQty ie TotQty = total quantity
k = k + Qty(i)
if k < TotQty or k = TotQty Then
j = [Qty(i) * CostPrice(i) + j
else
k > TotQty
m = TotQty - (k - Qty(i))
j =[[m * costprice(i)] + j
end if
wend
AvgPrice j/TotQty

Please help me out. You can give a sample code in whichever way.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 15:40:11

are you looking for t-sql or vb code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

folumike
Starting Member

24 Posts

Posted - 2012-05-20 : 18:07:54

Any one

quote:
Originally posted by visakh16

are you looking for t-sql or vb code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 19:30:31

hmm...your posted code is a bit confusing. i cant see whats initial value of j. also whats AvgPrice j/TotQty?

it would be better if you can post some sample data and explain what you want as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

folumike
Starting Member

24 Posts

Posted - 2012-05-21 : 17:08:47

TotQty = Balance as at date

See table below
Date | QtyForValuation | APInv# | CostPrice
------------------------------------------------------------
BegBalDate | BegBal(QtySupplied) | 'BegBal' | BegPrice | J1
APDate(1) | QtyReceived - QtySup | Inv# | ItemPrice | .
. | . | . | . | .
. | . | . | . | .
APDate(n) | n | n | n | Jn

Declare i, j , k, m
k = 0
i = 1
i=1
Do while k<=TotQty ie TotQty = total quantity
k = k + Qty(i)
if k < TotQty or k = TotQty Then
j = [Qty(i) * CostPrice(i) + j
else
k > TotQty
m = TotQty - (k - Qty(i))
j =[[m * costprice(i)] + j
end if
wend
AvgPrice = j/TotQty

NOTE
I want to do the valuation and then get the Average price (AvgPrice) as at the selected date.
Which is the array vakue divided by TotQty
AvgPrice = j/TotQty
TotQty = Balance as at date
J is the value for the array

quote:
Originally posteod by visakh16

hmm...your posted code is a bit confusing. i cant see whats initial value of j. also whats AvgPrice j/TotQty?

it would be better if you can post some sample data and explain what you want as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

folumike
Starting Member

24 Posts

Posted - 2012-05-21 : 17:30:22

from the table below, Initial values of J are the Begining balances such as opening stock date (BegBal Date), Opeining stock, Price for the opening stock(cost price

quote:
Originally posted by folumike

TotQty = Balance as at date

See table below
Date | QtyForValuation | APInv# | CostPrice
------------------------------------------------------------
12/jun/2011| 30 | 'BegBal' | 70.00 | J1
APDate(1) | 28 | Inv# | 75.00 | .
. | . | . | . | .
. | . | . | . | .
APDate(n) | n | n | n | Jn

J is an array to find stock valuation price as at the selected date.
Output is Average Price = Jn divided by current stock balance
e.g. AvgPrice = Jn/TotQty
for example; AvgPrice = Jn/45

Declare i, j , k, m
k = 0
i = 1
i=1
Do while k<=TotQty ie TotQty = total quantity
k = k + Qty(i)
if k < TotQty or k = TotQty Then
j = [Qty(i) * CostPrice(i) + j
else
k > TotQty
m = TotQty - (k - Qty(i))
j =[[m * costprice(i)] + j
end if
wend
AvgPrice = j/TotQty

NOTE
I want to do the valuation and then get the Average price (AvgPrice) as at the selected date.
Which is the array vakue divided by TotQty
AvgPrice = j/TotQty
TotQty = Balance as at date
J is the value for the array

quote:
Originally posteod by visakh16

hmm...your posted code is a bit confusing. i cant see whats initial value of j. also whats AvgPrice j/TotQty?

it would be better if you can post some sample data and explain what you want as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

folumike
Starting Member

24 Posts

Posted - 2012-05-21 : 18:05:48

I dont know how to explain further since I cant sent graphics. Or can you give me a sample code on how to calculate inventory valuation price and how to calculate its average price

[quote]Originally posted by folumike

from the table below, Initial values of J are the Begining balances such as opening stock date (BegBal Date), Opeining stock, Price for the opening stock(cost price
[quote]Originally posted by folumike

TotQty = Balance as at date

See table below
Date | QtyForValuation | APInv# | CostPrice
------------------------------------------------------------
12/jun/2011| 30 | 'BegBal' | 70.00 | J1
APDate(1) | 28 | Inv# | 75.00 | .
. | . |

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 18:42:04

See how to post proper data for question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources