Average Price/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

SQL Server 2000 Forums

Transact-SQL (2000)

Average Price/Valuation

Author

Topic

noblemfd
Starting Member

38 Posts

Posted - 2013-05-16 : 23:34:39

I have written this before, but was told to make it clearer:
(My company is using MSSQL SERVER 2000)
Table1: InvoiceDetail
Table2: StockCard
CREATE TABLE [dbo].[invoicedetail](
[serialno] [bigint] IDENTITY(1,1) NOT NULL,
[itemid] [nvarchar](50) NULL,
[date1] [datetime] NULL,
[qty] [float] NULL,
[inv#] [nvarchar](50) NULL,
[price] [money] NULL

) ON [PRIMARY]

INSERT invoicedetail (itemid, date1, qty, inv#, price)
VALUES ('0001', '01/01/2013', 10, 'INV001', 30)
VALUES ('0002', '01/01/2013', 5, 'INV001', 50)
VALUES ('0001', '01/01/2013', 10, 'INV001', 32)
VALUES ('0001', '02/01/2013', 5, 'INV001', 32)
VALUES ('0003', '01/01/2013', 10, 'INV001', 15)
VALUES ('0003', '04/01/2013', 20, 'INV001', 15)
VALUES ('0002', '11/01/2013', 10, 'INV001', 55)
VALUES ('0001', '11/01/2013', 10, 'INV001', 35)
VALUES ('0001', '18/01/2013', 20, 'INV001', 32)
VALUES ('0002', '20/01/2013', 10, 'INV001', 55)
VALUES ('0003', '20/01/2013', 10, 'INV001', 17)

CREATE TABLE [dbo].[stockcard](
[serialno] [bigint] IDENTITY(1,1) NOT NULL,
[itemid] [nvarchar](50) NULL,
[date1] [datetime] NULL,
[qtyIN] [float] NULL,
[qtyIN] [float] NULL

) ON [PRIMARY]

I want to use stored procedure to get the average price(stock valuation based on selected dates.
Once the parameters are entered, it wll go to table1(invoicedetail table) and check the concerned date

This is the formula my boss gave me
Formula:
AveragePrice = ItemValue/ItemBalance
NOTE THAT(=> as per chosen date):
ItemValue(from table1:invoicedetail) = qty * price
ItemBalance(from table2:stockcard) = SUM(qtyIN - qtyOUT)
i HAVE DONE IT IN VB6 Using array, but need to transform it to MS SQL SERVER 2000. Please help

I need Something like this ...
CREATE PROCEDURE Valuation(
@StartDate DATETIME, @EndDate DATETMIE, @ItemID)
AS...
.
.

EXPECTED OUTPUT:
ItemID | AvgPrice | TotalQtyIN | TotalQtyOUT | TotalBalnce

VB6 SAMPLE

Count_i = 0
Do While Val(cumQty) <= Val(ItmBal1)
cumQty = Val(cumQty) + (Stock_Array(Count_i, 2))
If Val(cumQty) <= Val(ItmBal1) Then
itmVal = Stock_Array(Count_i, 2) * Val(Stock_Array(Count_i, 4)) + itmVal
Else
ItmVal2 = Val(ItmBal1) - (cumQty - Stock_Array(Count_i, 2))
itmVal = (ItmVal2 * Val(Stock_Array(Count_i, 4))) + itmVal
Exit Do
End If
If Count_i = Count_J - 1 Then Exit Do

Count_i = Count_i + 1
Loop
getAvgPrice = 0
If Val(ItmBal1) <> 0 Then
getAvgPrice = Val(itmVal) / Val(ItmBal1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:21:00

[code]
CREATE PROCEDURE Valuation
@StartDate DATETIME, @EndDate DATETMIE, @ItemID int
AS
SELECT m.itemid,SUM(ItemValue)/NULLIF(SUM(ItemBalance,0)
FROM
(
SELECT itemid,[date1],
SUM(qty * price) AS ItemValue
FROM invoicedetail i
WHERE i.[date1] >= @startdate AND i.[date1] < @enddate+1
AND i.[itemid] = @ItemID
GROUP BY itemid,[date1]
)m
INNER JOIN
(
SELECT itemid,[date1],
SUM([qtyIN]-[qtyOUT]) AS ItemBalance
FROM [dbo].[stockcard]
GROUP BY itemid,[date1]
)n
ON n.itemid = m.itemid
AND n.[date1] = m.[date1]
GROUP BY m.itemid
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

folumike
Starting Member

24 Posts

Posted - 2013-05-21 : 15:05:08

Great! Am very grateful. I have one more question. What is the replacement for a "For Next Loop" in MSSQL 2000. Can u give a brief example?

quote:

Originally posted by visakh16


CREATE PROCEDURE Valuation
@StartDate DATETIME, @EndDate DATETMIE, @ItemID int
AS
SELECT m.itemid,SUM(ItemValue)/NULLIF(SUM(ItemBalance,0)
FROM
(
SELECT itemid,[date1],
SUM(qty * price) AS ItemValue
FROM invoicedetail i
WHERE i.[date1] >= @startdate AND i.[date1] < @enddate+1
AND i.[itemid] = @ItemID
GROUP BY itemid,[date1]
)m
INNER JOIN
(
SELECT itemid,[date1],
SUM([qtyIN]-[qtyOUT]) AS ItemBalance
FROM [dbo].[stockcard]
GROUP BY itemid,[date1]
)n
ON n.itemid = m.itemid
AND n.[date1] = m.[date1]
GROUP BY m.itemid
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:59:05

loops in SQL 2000 are implemented using WHILE or using a curor.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-22 : 04:25:50

but you shouldn't use them in almost any circumstance (bar admin) -- what's the use case. There will almost certainly be a better declarative way to do it.

also -- sql server 2000 is pretty damn long in the tooth now. Any chance to upgrade? 2005 has a lot of improvements over 2000.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

noblemfd
Starting Member

38 Posts

Posted - 2013-05-27 : 22:21:57

There is an error code that the aggregate allows only one argument. And this came from NULLIF(SUM(ItemBalance,0). Please help

quote:
Originally posted by folumike

Great! Am very grateful. I have one more question. What is the replacement for a "For Next Loop" in MSSQL 2000. Can u give a brief example?

quote:
Originally posted by visakh16

CREATE PROCEDURE Valuation
@StartDate DATETIME, @EndDate DATETMIE, @ItemID int
AS
SELECT m.itemid,SUM(ItemValue)/NULLIF(SUM(ItemBalance,0)
FROM
(
SELECT itemid,[date1],
SUM(qty * price) AS ItemValue
FROM invoicedetail i
WHERE i.[date1] >= @startdate AND i.[date1] < @enddate+1
AND i.[itemid] = @ItemID
GROUP BY itemid,[date1]
)m
INNER JOIN
(
SELECT itemid,[date1],
SUM([qtyIN]-[qtyOUT]) AS ItemBalance
FROM [dbo].[stockcard]
GROUP BY itemid,[date1]
)n
ON n.itemid = m.itemid
AND n.[date1] = m.[date1]
GROUP BY m.itemid
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 00:53:54

its a typo. it should be


CREATE PROCEDURE Valuation
@StartDate DATETIME, @EndDate DATETMIE, @ItemID int
AS
SELECT m.itemid,SUM(ItemValue)/NULLIF(SUM(ItemBalance),0)
FROM
(
SELECT itemid,[date1],
SUM(qty * price) AS ItemValue
FROM invoicedetail i
WHERE i.[date1] >= @startdate AND i.[date1] < @enddate+1
AND i.[itemid] = @ItemID
GROUP BY itemid,[date1]
)m
INNER JOIN
(
SELECT itemid,[date1],
SUM([qtyIN]-[qtyOUT]) AS ItemBalance
FROM [dbo].[stockcard]
GROUP BY itemid,[date1]
)n
ON n.itemid = m.itemid
AND n.[date1] = m.[date1]
GROUP BY m.itemid
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources