SQL Server Forums
Profile | Register | 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
 New Topic  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
52249 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
52249 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
52249 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  
 New 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