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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Average Price/Valuation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noblemfd
Starting Member

Nigeria
34 Posts

Posted - 05/16/2013 :  23:34:39  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/17/2013 :  00:21:00  Show Profile  Reply with Quote

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
Go to Top of Page

folumike
Starting Member

24 Posts

Posted - 05/21/2013 :  15:05:08  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/22/2013 :  00:59:05  Show Profile  Reply with Quote
loops in SQL 2000 are implemented using WHILE or using a curor.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/22/2013 :  04:25:50  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

noblemfd
Starting Member

Nigeria
34 Posts

Posted - 05/27/2013 :  22:21:57  Show Profile  Reply with Quote
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




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/28/2013 :  00:53:54  Show Profile  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000