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 |
|
|