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

Item Valuation

Author

Topic

folumike
Starting Member

24 Posts

Posted - 2013-12-17 : 19:58:38

We use VB6 and MSSQL 2000 in my Office. I want to use MSSQL 2000 to the this work below.
I have to tables:
1. tblTotalQty
2. tblPriceHistory
tblPriceHistory
RowID TransDate ItemID Quantity Price
1 01/01/2013 000001 20 500
2 01/01/2013 000002 30 200
3 02/01/2013 000003 50 300
4 02/01/2013 000002 30 300
5 04/02/2013 000001 50 500
6 05/02/2013 000002 50 300
7 12/03/2013 000002 10 600
8 12/04/2013 000003 20 200
9 13/04/2013 000001 10 400
10 20/04/2013 000001 30 100
11 25/05/2013 000003 20 300
12 03/06/2013 000002 40 200
13 22/06/2013 000001 50 400
14 30/06/2013 000002 10 200

tblTotalQty
Date1 ItemID TotQty
01/01/2013 000001 20
01/01/2013 000002 30
02/01/2013 000003 50
02/01/2013 000002 30
04/02/2013 000001 50
05/02/2013 000002 50
12/03/2013 000002 10
12/04/2013 000003 20
13/04/2013 000001 10
20/04/2013 000001 30

tblTotalQty is to show the Total Quantity of the items as at the current date selected while tblItemHistory is to show the price history of the Item Selected.
I want to calculate valuation price using While loop or Cursor
cumQty is cummulative quantity from tblItemHistory as at selected date
TotQty is total quantity from tblTotalQty as at date

While cumQty <= TotQty
cumQty = Val(cumQty) + Quantity
If cumQty <= TotQty Then
itmVal = Quantity * Price + itmVal
Else
itmVal = (((TotQty - (cumQty - Quantity)) * Price) + itmVal
Exit
End If

AvgPrice = itmVal / TotQty

Please help me my boss in on my neck
How do I write this query in MSSQL

NOTE -
AvePrice is the Average Price (final result)
TotQty is the Total Quantity from tblTotalQty
Quantity is the Quantity from tblPriceHistory
Quantity is the Price from tblPriceHistory

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 07:54:50

can you post what should be your end output?

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

folumike
Starting Member

24 Posts

Posted - 2013-12-18 : 10:22:17

THE FINAL OUTPUT WILL HAVE VALUES IN ITEMID & AVGPRICE. FOR EXAMPLE | 000001| 52, 000002| 60 AND SO ON.

folumike
Starting Member

24 Posts

Posted - 2013-12-18 : 10:22:24

THE FINAL OUTPUT WILL HAVE VALUES IN ITEMID & AVGPRICE. FOR EXAMPLE | 000001| 52, 000002| 60 AND SO ON.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 04:07:54

Learn how to post a question with proper data and output. Without that its difficult to understand what your exact reuqirements are
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

folumike
Starting Member

24 Posts

Posted - 2013-12-19 : 16:19:40

quote:
Originally posted by visakh16

Learn how to post a question with proper data and output. Without that its difficult to understand what your exact reuqirements are
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

The aim is to calculate Average Price(AvgPrice) of the stock valuation using two tables:ArrayUnion and StockBalance.
AvgPrice = itemValue / itemBalance
itemValue is got by iterating through ArrayUnion table as per selected date.
itemBalance is got from StockBalance table
cumQty is the cummulative quantity of the arrayunion table as per the selected ItemID AND date

I expected a Output like this, but not exactly

But am getting
ItemID AvgPrice
1 6170
2 6754.02
3 8765.34
4 2052.73
5 7078.13
6 5283.06
.
.
.

But am getting
ItemID AvgPrice
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
.
.
.
See the script below

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArrayUnion]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ArrayUnion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StockBalance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StockBalance]
GO

CREATE TABLE [dbo].[ArrayUnion] (
[ItemX] [int] IDENTITY(1,1) NULL ,
[IemID] [int] NULL ,
[Date1] [datetime] NULL ,
[QtySupBal] [float] NULL ,
[InvID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Price] [money] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StockBalance] (
[ItemID] [int] NULL ,
[ItemBal] [float] NULL
) ON [PRIMARY]
GO

INSERT ArrayUnion VALUES(1,1,'01/01/2013','BEGBAL',6192.52)
INSERT ArrayUnion VALUES(2,2,'01/01/2013','BEGBAL',6766.8)
INSERT ArrayUnion VALUES(3,2,'02/01/2013','95422569',6766.8)
INSERT ArrayUnion VALUES(4,2,'02/01/2013','95422100',6792.8)
INSERT ArrayUnion VALUES(5,3,'03/01/2013','BEGBAL',9571.52)
INSERT ArrayUnion VALUES(6,3,'05/01/2013','BEGBAL',6192.58)
INSERT ArrayUnion VALUES(7,3,'05/01/2013','6900019885',9792.52)
INSERT ArrayUnion VALUES(8,4,'06/01/2013','BEGBAL',2182.22)
INSERT ArrayUnion VALUES(9,5,'07/01/2013','BEGBAL',7194.52)
INSERT ArrayUnion VALUES(10,6,'08/01/2013','BEGBAL',5430.89)
INSERT ArrayUnion VALUES(11,6,'14/01/2013','95422588',5430.89)

INSERT StockBalance VALUES(1,3277)
INSERT StockBalance VALUES(2,4554)
INSERT StockBalance VALUES(3,6785)
INSERT StockBalance VALUES(4,1824)
INSERT StockBalance VALUES(5,5993)
INSERT StockBalance VALUES(6,2792)

CREATE TABLE StockValuation(
ItemID NVARCHAR(100),
AvgPrice MONEY)

INSERT INTO StockValuation(ItemID, AvgPrice)
SELECT DISTINCT ItemID, NULL
FROM ArrayUnion
ORDER BY ItemID

/* declare the local variables */
DECLARE @ItemIDX NVARCHAR(100), @AvgPriceX MONEY

DECLARE @itmBal FLOAT(8), @itmVal FLOAT(8), @cumQty FLOAT(8)
DECLARE @ItemID NVARCHAR(100), @Date1 DATETIME, @QtySupBal FLOAT(8), @Price MONEY

--SET @AvgPrice = 0
SET @cumQty = 0
SET @itmBal = (SELECT ItemBal FROM StockBalance)
DECLARE curAvgPrice CURSOR FAST_FORWARD FOR
SELECT ItemID, AvgPrice
FROM StockValuation
ORDER BY ItemID
OPEN curAvgPrice

/* This is executed as long as the previous fetch succeeds. */
FETCH NEXT FROM curAvgPrice INTO @ItemIDX, @AvgPriceX

WHILE (@@FETCH_STATUS = 0) -- whilst all is well
BEGIN
UPDATE StockValuation SET avgPrice = @itmVal / NULLIF(@itmBal, 0);
DECLARE curValuation CURSOR FAST_FORWARD FOR
SELECT ItemID, Date1, QtySupBal, Price, AvgPrice
FROM ArrayUnion
ORDER BY ItemID, Date1
OPEN curValuation
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM curValuation INTO @ItemID, @Date1, @QtySupBal, @Price, @AvgPrice
WHILE (@@FETCH_STATUS = 0) AND (@cumQty <= @itmBal) --whilst all is well & Cummulative Qty = Total Qty as at selected Date
BEGIN
SET @cumQty = @cumQty + @QtySupBal
IF @cumQty <= @ItmBal --current record isnt sufficient, use it and move on
BEGIN
UPDATE StockValuation SET avgPrice = @itmVal / NULLIF(@itmBal, 0);
SET @itmVal = (@QtySupBal * @Price) + @itmVal
END
ELSE
BEGIN
UPDATE StockValuation SET avgPrice = @itmVal / NULLIF(@itmBal, 0);
SET @itmVal = ((@itmBal - (@cumQty - @QtySupBal)) * @Price) + @itmVal
END
FETCH NEXT FROM curValuation INTO @ItemID, @Date1, @QtySupBal, @Price, @AvgPrice
END
CLOSE curValuation
DEALLOCATE curValuation
FETCH NEXT FROM curAvgPrice INTO @ItemIDX, @AvgPriceX
END
CLOSE curAvgPrice
DEALLOCATE curAvgPrice

folumike
Starting Member

24 Posts

Posted - 2013-12-19 : 16:21:59

quote:
Originally posted by visakh16

Learn how to post a question with proper data and output. Without that its difficult to understand what your exact reuqirements are
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

Am very sorry. See the detail. Please help

The aim is to calculate Average Price(AvgPrice) of the stock valuation using two tables:ArrayUnion and StockBalance.
AvgPrice = itemValue / itemBalance
itemValue is got by iterating through ArrayUnion table as per selected date.
itemBalance is got from StockBalance table
cumQty is the cummulative quantity of the arrayunion table as per the selected ItemID AND date

I expected a Output like this, but not exactly

ItemID AvgPrice
1 6170
2 6754.02
3 8765.34
4 2052.73
5 7078.13
6 5283.06
.
.
.

But am getting
ItemID AvgPrice
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
.
.
.
See the script below

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArrayUnion]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ArrayUnion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StockBalance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StockBalance]
GO

CREATE TABLE [dbo].[ArrayUnion] (
[ItemX] [int] IDENTITY(1,1) NULL ,
[IemID] [int] NULL ,
[Date1] [datetime] NULL ,
[QtySupBal] [float] NULL ,
[InvID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Price] [money] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StockBalance] (
[ItemID] [int] NULL ,
[ItemBal] [float] NULL
) ON [PRIMARY]
GO

INSERT ArrayUnion VALUES(1,1,'01/01/2013','BEGBAL',6192.52)
INSERT ArrayUnion VALUES(2,2,'01/01/2013','BEGBAL',6766.8)
INSERT ArrayUnion VALUES(3,2,'02/01/2013','95422569',6766.8)
INSERT ArrayUnion VALUES(4,2,'02/01/2013','95422100',6792.8)
INSERT ArrayUnion VALUES(5,3,'03/01/2013','BEGBAL',9571.52)
INSERT ArrayUnion VALUES(6,3,'05/01/2013','BEGBAL',6192.58)
INSERT ArrayUnion VALUES(7,3,'05/01/2013','6900019885',9792.52)
INSERT ArrayUnion VALUES(8,4,'06/01/2013','BEGBAL',2182.22)
INSERT ArrayUnion VALUES(9,5,'07/01/2013','BEGBAL',7194.52)
INSERT ArrayUnion VALUES(10,6,'08/01/2013','BEGBAL',5430.89)
INSERT ArrayUnion VALUES(11,6,'14/01/2013','95422588',5430.89)

INSERT StockBalance VALUES(1,3277)
INSERT StockBalance VALUES(2,4554)
INSERT StockBalance VALUES(3,6785)
INSERT StockBalance VALUES(4,1824)
INSERT StockBalance VALUES(5,5993)
INSERT StockBalance VALUES(6,2792)

CREATE TABLE StockValuation(
ItemID NVARCHAR(100),
AvgPrice MONEY)

INSERT INTO StockValuation(ItemID, AvgPrice)
SELECT DISTINCT ItemID, NULL
FROM ArrayUnion
ORDER BY ItemID

/* declare the local variables */
DECLARE @ItemIDX NVARCHAR(100), @AvgPriceX MONEY

DECLARE @itmBal FLOAT(8), @itmVal FLOAT(8), @cumQty FLOAT(8)
DECLARE @ItemID NVARCHAR(100), @Date1 DATETIME, @QtySupBal FLOAT(8), @Price MONEY

--SET @AvgPrice = 0
SET @cumQty = 0
SET @itmBal = (SELECT ItemBal FROM StockBalance)
DECLARE curAvgPrice CURSOR FAST_FORWARD FOR
SELECT ItemID, AvgPrice
FROM StockValuation
ORDER BY ItemID
OPEN curAvgPrice

/* This is executed as long as the previous fetch succeeds. */
FETCH NEXT FROM curAvgPrice INTO @ItemIDX, @AvgPriceX

WHILE (@@FETCH_STATUS = 0) -- whilst all is well
BEGIN
UPDATE StockValuation SET avgPrice = @itmVal / NULLIF(@itmBal, 0);
DECLARE curValuation CURSOR FAST_FORWARD FOR
SELECT ItemID, Date1, QtySupBal, Price, AvgPrice
FROM ArrayUnion
ORDER BY ItemID, Date1
OPEN curValuation
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM curValuation INTO @ItemID, @Date1, @QtySupBal, @Price, @AvgPrice
WHILE (@@FETCH_STATUS = 0) AND (@cumQty <= @itmBal) --whilst all is well & Cummulative Qty = Total Qty as at selected Date
BEGIN
SET @cumQty = @cumQty + @QtySupBal
IF @cumQty <= @ItmBal --current record isnt sufficient, use it and move on
BEGIN
UPDATE StockValuation SET avgPrice = @itmVal / NULLIF(@itmBal, 0);
SET @itmVal = (@QtySupBal * @Price) + @itmVal
END
ELSE
BEGIN
UPDATE StockValuation SET avgPrice = @itmVal / NULLIF(@itmBal, 0);
SET @itmVal = ((@itmBal - (@cumQty - @QtySupBal)) * @Price) + @itmVal
END
FETCH NEXT FROM curValuation INTO @ItemID, @Date1, @QtySupBal, @Price, @AvgPrice
END
CLOSE curValuation
DEALLOCATE curValuation
FETCH NEXT FROM curAvgPrice INTO @ItemIDX, @AvgPriceX
END
CLOSE curAvgPrice
DEALLOCATE curAvgPrice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources