Calculate Percentage based on values.

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)

Calculate Percentage based on values.

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-01-04 : 12:09:07

Hello,

I have a problem in our front-end application that needs to be temporarily remedied via a query or trigger (preferrably). I have a table called PromoLocation that includes the following columns:
===============================================
= PromoID | SkuID | LocID | PromoPrice | DiscLevel | rowguid =
===============================================

NOTE: The rowguid is used because this database is being replicated.

Each promotion is assigned a PromoID, and I assign a location to the LocID column to apply prices (PromoPrice or DiscLevel) to each SkuID. The PromoPrice is a dollar value (i.e. 165.00), which is derived from the LocationPrice column in our ProductLocation table (unless the PromoPrice is a custom price, which is entered either in this column manually or via our front-end app), while the DiscLevel is a percentage of the dollar value. If I manually enter the PromoPrice (let's say from the LocationPrice of 165.00 to 150.00) then any value in the DiscLevel is disregarded.

My problem is that our front-end application calculates the sale price if the DiscLevel is set as an exact amount instead of a whole dollar amount. If my LocationPrice changes, let's say from 165.00 to 159.00, then the percentage in the DiscLevel will ultimately be calculated in our front-end, using 20% as the sale percentage, as 127.20. We need all prices rounded to the nearest whole dollar, but our front-end app does not contain code to do that.

I want to set the DiscLevel to a percentage, but by doing so, my sale price must ultimately be a whole dollar amount. For example, if I want to set the DiscLevel of a SkuID with an original value of $24.00 to 20%, this would calculate to $19.20. I want either the PromoPrice to be set to 19.00 or set the DiscLevel to 20.8333333%, which is the actual percentage for the rounded PromoPrice.

Is there a way I can create a query or trigger to calculate the true sale price using the PromoPrice and DiscLevel, round the value of PromoPrice to a whole dollar amount, and either update the PromoPrice with that value, or calculate the corrected DiscLevel percentage and update the DiscLevel value?

Thanks for all your help.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 12:57:15

My Algebra must be shaky...If @x is the original price, and @y is the original discount, @a will be the new calculated price...got that part...now we need to solve for @b. If you substitute @y with @b, then solve for @b, that should do it.

But I think (No, to be fair, I know) that it's not right.


DECLARE @x money, @y real, @a money, @b real
SELECT @x = 24, @y = 20
SELECT @a = FLOOR(@x - (@x * (@y/100))+.5)
SELECT @a
SELECT @b = 100*((@a-@x-.5)/@x)
SELECT @b

If you can figure out how to solve for @b, then It's easy (although reading the explination you gave took me awhile)

And I would update both, not one or the other for reasons of data consistency...you could get out of whack pretty quickly.

Also I think I would have a trigger to write all of the changes to an audit table.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 13:03:37

I got it...simplified it and I saw I forgot to reverse the sign


DECLARE @x money, @y real, @a money, @b real
SELECT @x = 24, @y = .20
SELECT @a = FLOOR(@x - (@x * @y))
SELECT @a
SELECT @b = ((@a-@x)/@x)*-1
SELECT @b

You have to ask if you want to round up or round down by adding the .5

Let me see what I can do with a TRIGGER

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 13:57:25

Got a bug, but I was thinking along these lines


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(pkey int IDENTITY(1,1), PromoPrice money, DiscLevel decimal(38,37))
CREATE TABLE myTable99_H(AuditDate datetime, pkey int, PromoPrice money, DiscLevel decimal(38,37))
GO

INSERT INTO myTable99(PromoPrice, DiscLevel)
SELECT 24, .20 UNION ALL
SELECT 100, .25 UNION ALL
SELECT 1000, .33 UNION ALL
SELECT 50000, .50
GO

SELECT * FROM myTable99
GO

CREATE TRIGGER myTrigger99 ON myTable99 FOR UPDATE
AS
  BEGIN
	INSERT INTO myTable99_H (AuditDate, pkey, PromoPrice, DiscLevel)
	SELECT GetDate(), pkey, PromoPrice, DiscLevel
	  FROM inserted 

	UPDATE t
	   SET DiscLevel = CONVERT(decimal(38,37),((a.PromoPrice - x.PromoPrice)/x.PromoPrice)* -1)
	  FROM deleted a
    INNER JOIN inserted x
	    ON a.pkey = x.pkey
    INNER JOIN myTable99 t
	    ON t.pkey = x.pkey
	 WHERE a.PromoPrice <> x.PromoPrice

	UPDATE t 
	   SET PromoPrice = CONVERT(money,FLOOR(x.PromoPrice - (x.PromoPrice/x.DiscLevel)))
	  FROM deleted a
    INNER JOIN inserted x
	    ON a.pkey = x.pkey
    INNER JOIN myTable99 t
	    ON t.pkey = x.pkey
	 WHERE a.DiscLevel <> x.DiscLevel
  END
GO

UPDATE myTable99 SET PromoPrice = 80 WHERE pkey = 2
SELECT * FROM myTable99
SELECT * FROM myTable99_H

--UPDATE myTable99 SET DiscLevel = .10 WHERE pkey = 1
--SELECT * FROM myTable99
--SELECT * FROM myTable99_H
-- The above fails with:
--
--Server: Msg 8115, Level 16, State 2, Procedure myTrigger99, Line 18
--Arithmetic overflow error converting expression to data type numeric.
--The statement has been terminated.
--  I'll keep trying to figure out why
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
DROP TABLE myTable99_H
GO

Brett

8-)

uxphreak
Starting Member

38 Posts

Posted - 2005-01-04 : 14:58:17

Hey X002548, thanks for all of this so far... I'm relatively new to SQL, so I'm still trying to digest your latest reply.

I used the following code derived from your first reply, which gives me the correct value for the percentage amount to be usedin the DiscLevel column:

DECLARE @x money, @y real, @a money, @b real
SELECT @x = 24, @y = 20
SELECT @a = FLOOR(@x - (@x * (@y/100))+.5)
SELECT @a
SELECT @b = ((@a-@x-.5)/@x)*-1
SELECT @b

As I mentioned, I'm a newbie and stil going over your last reply, but this is definitely what I am looking for so far. Your work is very much appreciated.

D

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 15:12:42

You can cut and paste all of the code in to Query Analyzer and it'll just run...

So you can see the results...still got that one problem though

EDIT: And enable your emails in your profile

Brett

8-)

uxphreak
Starting Member

38 Posts

Posted - 2005-01-04 : 17:48:36

I've enabled e-mails in my profile.

D

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-05 : 11:41:56

[dooh]
The insert into to history should be done from deleted...
[/dooh]


	INSERT INTO myTable99_H (AuditDate, pkey, PromoPrice, DiscLevel)
	SELECT GetDate(), pkey, PromoPrice, DiscLevel
	  FROM deleted

Still trying to figure out the conversion problem...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-05 : 11:45:20

OK, Got rid of the overflow problem. Now I think the math is wrong


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(pkey int IDENTITY(1,1), PromoPrice money, DiscLevel decimal(38,16))
CREATE TABLE myTable99_H(AuditDate datetime, pkey int, PromoPrice money, DiscLevel decimal(38,16))
GO

INSERT INTO myTable99(PromoPrice, DiscLevel)
SELECT 24, .20 UNION ALL
SELECT 100, .25 UNION ALL
SELECT 1000, .33 UNION ALL
SELECT 50000, .50
GO

SELECT * FROM myTable99
GO

CREATE TRIGGER myTrigger99 ON myTable99 FOR UPDATE
AS
  BEGIN
	INSERT INTO myTable99_H (AuditDate, pkey, PromoPrice, DiscLevel)
	SELECT GetDate(), pkey, PromoPrice, DiscLevel
	  FROM deleted 

	UPDATE t
	   SET DiscLevel = CONVERT(decimal(38,16),((a.PromoPrice - x.PromoPrice)/x.PromoPrice)* -1)
	  FROM deleted a
    INNER JOIN inserted x
	    ON a.pkey = x.pkey
    INNER JOIN myTable99 t
	    ON t.pkey = x.pkey
	 WHERE a.PromoPrice <> x.PromoPrice

	UPDATE t 
	   SET PromoPrice = CONVERT(money,FLOOR(x.PromoPrice - (x.PromoPrice/x.DiscLevel)))
	  FROM deleted a
    INNER JOIN inserted x
	    ON a.pkey = x.pkey
    INNER JOIN myTable99 t
	    ON t.pkey = x.pkey
	 WHERE a.DiscLevel <> x.DiscLevel
  END
GO

UPDATE myTable99 SET PromoPrice = 80 WHERE pkey = 2
SELECT * FROM myTable99
SELECT * FROM myTable99_H

UPDATE myTable99 SET DiscLevel = .10 WHERE pkey = 1
SELECT * FROM myTable99
SELECT * FROM myTable99_H
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
DROP TABLE myTable99_H
GO

Brett

8-)

uxphreak
Starting Member

38 Posts

Posted - 2005-01-05 : 12:30:18

Brett, here's the DDL of the tables involved. Promotion is the "Parent" table, which contains data pertaining to each promotion created, while PromoLocation is the "Child" table, which contains item and price data for each promotion.

Thanks again, D.

CREATE TABLE [dbo].[PromoLocation] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PromoPrice] [money] NULL ,
[DiscLevel] [float] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Promotion] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PromoLocation] WITH NOCHECK ADD
CONSTRAINT [PK_PromoLocation] PRIMARY KEY CLUSTERED
(
[PromoID],
[LocID],
[SkuID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Promotion] WITH NOCHECK ADD
CONSTRAINT [Promotion_PK] PRIMARY KEY CLUSTERED
(
[PromoID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PromoLocation] ADD
CONSTRAINT [DF__PromoLoca__rowgu__7A8729A3] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_117575457] ON [dbo].[PromoLocation]([rowguid]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Promotion] ADD
CONSTRAINT [DF__Promotion__rowgu__4707859D] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_213575799] ON [dbo].[Promotion]([rowguid]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PromoLocation] ADD
CONSTRAINT [Location_PromoLocation_FK1] FOREIGN KEY
(
[LocID]
) REFERENCES [dbo].[Location] (
[LocID]
),
CONSTRAINT [Product_PromoLocation_FK1] FOREIGN KEY
(
[SkuID]
) REFERENCES [dbo].[Product] (
[SkuID]
),
CONSTRAINT [Promotion_PromoLocation_FK1] FOREIGN KEY
(
[PromoID]
) REFERENCES [dbo].[Promotion] (
[PromoID]
)
GO

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-05 : 13:51:47

OK, what's the GUID for? I guess you could use that for the joins...but I did mine the "long way"

Still have to check in to the math, but that should be the easy part


USE Northwind
GO

CREATE TABLE x002548_PromoLocation (
[PromoID] [char] (10) NOT NULL ,
[LocID] [char] (16) NOT NULL ,
[SkuID] [char] (16) NOT NULL ,
[PromoPrice] [money] NULL ,
[DiscLevel] [float] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE x002548_PromoLocation WITH NOCHECK ADD 
CONSTRAINT [PK_PromoLocation] PRIMARY KEY CLUSTERED 
(
[PromoID],
[LocID],
[SkuID]
) ON [PRIMARY] 
GO

CREATE TABLE x002548_PromoLocation_H (
[Add_date] datetime NOT NULL DEFAULT (GetDate()) ,
[PromoID] [char] (10) NOT NULL ,
[LocID] [char] (16) NOT NULL ,
[SkuID] [char] (16) NOT NULL ,
[PromoPrice] [money] NULL ,
[DiscLevel] [float] NULL ,
[rowguid] varchar(40) NOT NULL 
) ON [PRIMARY]
GO

INSERT INTO x002548_PromoLocation(PromoID, LocID, SkuId, PromoPrice, DiscLevel, rowguid)
SELECT 'X', 'Here', 'A', 24, .20, NEWID() UNION ALL
SELECT 'X', 'There', 'B', 100, .25, NEWID() UNION ALL
SELECT 'Y', 'Everywhere', 'C', 1000, .33, NEWID() UNION ALL
SELECT 'Z', 'Margaritaville', 'D', 50000, .50, NEWID()
GO

SELECT * FROM x002548_PromoLocation
SELECT * FROM x002548_PromoLocation_H
GO

CREATE TRIGGER x002548_PromoLocation_TR_UPD ON x002548_PromoLocation FOR UPDATE
AS
  BEGIN
	INSERT INTO x002548_PromoLocation_H (Add_Date, PromoID, LocID, SkuId, PromoPrice, DiscLevel, rowguid)
	SELECT GetDate(), PromoID, LocID, SkuId, PromoPrice, DiscLevel, rowguid
	  FROM deleted 

	UPDATE t
	   SET DiscLevel = CONVERT(decimal(38,16),((a.PromoPrice - x.PromoPrice)/x.PromoPrice)* -1)
	  FROM deleted a
    INNER JOIN inserted x
	    ON a.PromoID = x.PromoID AND a.LocID = x.LocID AND a.SkuId = x.SkuId
    INNER JOIN x002548_PromoLocation t
	    ON t.PromoID = x.PromoID AND t.LocID = x.LocID AND t.SkuId = x.SkuId
	 WHERE a.PromoPrice <> x.PromoPrice

	UPDATE t 
	   SET PromoPrice = CONVERT(money,FLOOR(x.PromoPrice - (x.PromoPrice/x.DiscLevel)))
	  FROM deleted a
    INNER JOIN inserted x
	    ON a.PromoID = x.PromoID AND a.LocID = x.LocID AND a.SkuId = x.SkuId
    INNER JOIN x002548_PromoLocation t
	    ON t.PromoID = t.PromoID AND t.LocID = x.LocID AND t.SkuId = x.SkuId
	 WHERE a.DiscLevel <> x.DiscLevel
  END

GO


UPDATE x002548_PromoLocation SET PromoPrice = 80 WHERE PromoID = 'X' AND LocID = 'Here' AND SkuID = 'A'
SELECT * FROM x002548_PromoLocation
SELECT * FROM x002548_PromoLocation_H

UPDATE x002548_PromoLocation SET DiscLevel = .10 WHERE PromoID = 'X' AND LocID = 'There' AND SkuID = 'B'
SELECT * FROM x002548_PromoLocation
SELECT * FROM x002548_PromoLocation_H
GO

SET NOCOUNT OFF
DROP TRIGGER x002548_PromoLocation_TR_UPD
DROP TABLE x002548_PromoLocation
DROP TABLE x002548_PromoLocation_H
GO

Brett

8-)

uxphreak
Starting Member

38 Posts

Posted - 2005-01-05 : 14:09:48

rowguid is used for Replication, since this database uses merge replication.

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources