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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 inventory/stock valuation
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

folumike
Starting Member

24 Posts

Posted - 05/20/2012 :  15:24:30  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 05/20/2012 :  15:40:11  Show Profile  Reply with Quote
are you looking for t-sql or vb code?

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

Go to Top of Page

folumike
Starting Member

24 Posts

Posted - 05/20/2012 :  18:07:54  Show Profile  Reply with Quote
Any one

quote:
Originally posted by visakh16

are you looking for t-sql or vb code?

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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/20/2012 :  19:30:31  Show Profile  Reply with Quote
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/

Go to Top of Page

folumike
Starting Member

24 Posts

Posted - 05/21/2012 :  17:08:47  Show Profile  Reply with Quote
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/



Go to Top of Page

folumike
Starting Member

24 Posts

Posted - 05/21/2012 :  17:30:22  Show Profile  Reply with Quote
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/





Go to Top of Page

folumike
Starting Member

24 Posts

Posted - 05/21/2012 :  18:05:48  Show Profile  Reply with Quote
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 | .
. | . |
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/21/2012 :  18:42:04  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000