query help... - 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)

query help...

Author

Topic

BioMash
Starting Member

8 Posts

Posted - 2005-12-27 : 23:59:46

I have the following tables

CREATE TABLE [dbo].[tblProduct](
	[intProductID] [int] IDENTITY(1,1) NOT NULL,
	[strProductName] [nvarchar](200) COLLATE Latin1_General_CI_AS NOT NULL,
	[strProductCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[strProductDescription] [text] COLLATE Latin1_General_CI_AS NULL,
	[intProductTypeID] [int] NOT NULL,
	[intProductActive] [int] NOT NULL,
	[intOperatorID] [int] NOT NULL,
	[dteLastModified] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[tblValue](
	[intValueID] [int] IDENTITY(1,1) NOT NULL,
	[intCurrencyID] [int] NOT NULL,
	[intTaxID] [int] NOT NULL,
	[fltTaxPercent] [money] NOT NULL,
	[fltValueAmount] [money] NOT NULL,
 CONSTRAINT [PK_tblValue] PRIMARY KEY CLUSTERED 
(
	[intValueID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblProductValue](
	[intProductID] [int] NOT NULL,
	[intValueID] [int] NOT NULL,
 CONSTRAINT [PK_tblProductValue] PRIMARY KEY CLUSTERED 
(
	[intProductID] ASC,
	[intValueID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

each product can have many values. I need to make a price change of 7% accross the board and am writing a script to delete each row in the productvalue table and create a new value and assign that value to the product.

I would appreciate some help as I do not write much sql and although I have read up over the last few days and thought I was on the right track I cannot get the script below to work and it just runs indefinately without making any changes to the table:

I have the following script:


	declare @intProductID int
	declare @intValueID int
	declare @intOldValueID int
	declare @intCurrencyID int
	declare @intTaxID int
	declare @fltTaxPercent float
	declare @fltValueAmount float
	declare @NewValueAmount smallmoney

DECLARE c1 CURSOR FOR

Select	pv.intProductID,
		v.intValueID,
		v.intCurrencyID,
		v.intTaxID,
		v.fltTaxPercent,
		v.fltValueAmount

FROM tblProductValue pv, tblValue v
WHERE pv.intValueID = v.intValueID

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

SET @NewValueAmount = CEILING(@fltValueAmount + (@fltValueAmount * 0.07))

EXEC sp_Product_DeleteProductValue @intProductID = @intProductID, @intValueID = @intoldValueID

EXEC @intValueID = sp_Value_Insert @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_Assign @intProductID = @intProductID, @intValueID = @intValueID

END

CLOSE c1
DEALLOCATE c1

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-28 : 00:48:54

declare @intProductID int
declare @intValueID int
declare @intOldValueID int
declare @intCurrencyID int
declare @intTaxID int
declare @fltTaxPercent float
declare @fltValueAmount float
declare @NewValueAmount smallmoney

DECLARE c1 CURSOR FOR

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount

FROM tblProductValue pv, tblValue v
WHERE pv.intValueID = v.intValueID

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

SET @NewValueAmount = CEILING(@fltValueAmount + (@fltValueAmount * 0.07))

EXEC sp_Product_DeleteProductValue @intProductID = @intProductID, @intValueID = @intoldValueID

EXEC @intValueID = sp_Value_Insert @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_Assign @intProductID = @intProductID, @intValueID = @intValueID

---- add fetch statement
FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

---
END

CLOSE c1
DEALLOCATE c1

BioMash
Starting Member

8 Posts

Posted - 2005-12-28 : 01:20:05

doh! Thanks for that, dont know how I didn't catch it! The cursor now seems to work however the insert into the productvalue table is not, it is just inserting all the intvalueid's as 0

I presume it is a problem with the value insert and assigning the variable intvalueid.

these are my stored procs that are being called:

ALTER PROCEDURE [dbo].[sp_Value_Insert]
	@intValueID		int = NULL	OUTPUT,
	@intCurrencyID		int,
	@intTaxID		int,
	@fltTaxPercent		float,
	@fltValueAmount	float
AS
			INSERT INTO
				tblValue
				(
					intCurrencyID,
					intTaxID,
					fltTaxPercent,
					fltValueAmount
				)
			VALUES
				(
					@intCurrencyID,
					@intTaxID,
					@fltTaxPercent,
					@fltValueAmount
				)

		SET @intValueID = @@IDENTITY

ALTER PROCEDURE [dbo].[sp_ProductValue_Assign]
	@intProductID		int,
	@intValueID		int
AS
			INSERT INTO
				tblProductValue
				(
					intProductID,
					intValueID
				)
			VALUES
				(
					@intProductID,
					@intValueID
				)

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-28 : 01:45:00

declare @intProductID int
declare @intValueID int
declare @intOldValueID int
declare @intCurrencyID int
declare @intTaxID int
declare @fltTaxPercent float
declare @fltValueAmount float
declare @NewValueAmount smallmoney

DECLARE c1 CURSOR FOR

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount

FROM tblProductValue pv, tblValue v
WHERE pv.intValueID = v.intValueID

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

SET @NewValueAmount = CEILING(@fltValueAmount + (@fltValueAmount * 0.07))

EXEC sp_Product_DeleteProductValue @intProductID = @intProductID, @intValueID = @intoldValueID

~~EXEC @intValueID = sp_Value_Insert @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount~~

EXEC sp_Value_Insert @intValueID OUTPUT, @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_Assign @intProductID = @intProductID, @intValueID = @intValueID

---- add fetch statement
FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

---
END

CLOSE c1
DEALLOCATE c1

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-28 : 01:50:07

Hi BioMash,
Do you want to keep the old values in table tblvalue?
What is the need of deleting values from productvalue table and then inserting them again? why cant you just update the table with new value of value id?

BioMash
Starting Member

8 Posts

Posted - 2005-12-28 : 21:40:57

Hi

Thanks for all your help:) I have made the changes as you suggested and now update the productvalue table instead.

I wonder if you can help with another problem, at the moment I use ceiling to round upto the nearest one, is there a way to round up to the nearest 5 or 10...(eg if i have 123.45 it would round upto 125 instead of 124?

Thanks again and happy holidays!!

Bio

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 00:56:19

>>is there a way to round up to the nearest 5 or 10...(eg if i have 123.45 it would round upto 125 instead of 124?

Why?
Do you need something like this?

declare @t float
set @t='123.45'
select round(@t+1.5,1)

Madhivanan

Failing to plan is Planning to fail

BioMash
Starting Member

8 Posts

Posted - 2005-12-29 : 01:30:20

I want to give the admin person the option to choose the percentage increase and the whole number to round too.....so I have converted the query to a stored proc and added two more variables.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[sp_pricelist_update]
	@intRound int = null,
	@decPercentage	decimal = null,
	@intProductID int = null,
	@intValueID int = null,
	@intOldValueID int = null,
	@intCurrencyID int = null,
	@intTaxID int = null,
	@fltTaxPercent float = null,
	@fltValueAmount float = null,
	@NewValueAmount smallmoney = null
AS

DECLARE c1 CURSOR FOR

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount

FROM tblProductValue pv, tblValue v

WHERE pv.intValueID = v.intValueID

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

-- changed to allow passing in of percentage and whole number to round up to.

SET @NewValueAmount = CEILING((@fltValueAmount + (@fltValueAmount * @Percentage))/@intRound)*@intRound


EXEC sp_Value_Insert @intValueID OUTPUT, @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_NewAssign @intOldValueID = @intOldValueID, @intProductID = @intProductID, @intValueID = @intValueID

---- add fetch statement
FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

---
END

CLOSE c1
DEALLOCATE c1

It seems to work as I want....

Thank you all for your help! Much appreciated.

BioMash
Starting Member

8 Posts

Posted - 2005-12-29 : 01:49:31

hmmm....ok...maybe it doesn't!!!

SET @NewValueAmount = CEILING((@fltValueAmount + (@fltValueAmount * @decPercentage))/@intRound)*@intRound

I think this works but my insert on the value table is not adding the new value but just copying the old one!!! Any ideas??

Bio

BioMash
Starting Member

8 Posts

Posted - 2005-12-29 : 04:17:09

not to worry, I needed to set the precision and scale for decimal

Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 08:36:28

quote:
I wonder if you can help with another problem, at the moment I use ceiling to round upto the nearest one, is there a way to round up to the nearest 5 or 10...(eg if i have 123.45 it would round upto 125 instead of 124?

Yup

declare @t float
select @t = 123.45
select cast(round(@t / 5.0, 0) * 5 as int)

-----------------
[KH]
Learn something new everyday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources