Normalization and sales levels

When certain purchases are made, the commission rate depends on the size
of the purchase. We get data from an external source that shows the
expected commission rate, depending on the size of the purchase.
The commission rate/sales level data comes to us in an Excel file, and I
need to convert it to some appropriate SQL structure. (I don't have any
DDL yet because I haven't designed the tables.)
Here's an example of one commission level record that we get from the
external source:
ID, SalesLvl1, SalesLvl2, SalesLvl3, SalesLvl4, Rate1, Rate2, Rate3,
Rate4
1X123, 10000, 25000, 50000, 1E14, 0.05, 0.035, 0.025,
0.02
After I turn this into a sensible SQL table, then I'll see a sale of X
dollars for product ID 1X123, and I need to find the expected commission
level.
(Sales from .01 to SalesLvl1 (including = SalesLvl1) get Rate1; sales
above SalesLvl1 and less than or equal to SalesLvl2 get Rate2, etc. It
is expected that no sale will be greater than the last level.)
If I expand the incoming data into four records with the product ID, the
max sales level for that rate, and the rate, then I would be looking for
the "first" sales level that's larger than the purchase size, and then
get the rate. But that implies an ordering on the records, and I know
that SQL records don't intrinsically have an ordering.
Do I need to design each row to hold the min and the max sales level for
that rate, and then search for records where the sales level is between
that min and max? How can I prevent overlapping records?
I have searched the Web for SQL and commission, and also sales level,
and breakpoint (of course searching on SQL and breakpoint gives me
unrelated stuff), and I can't find examples. If anyone can point me to
examples of how to do this -- I'm sure it is not a new problem -- I
would be grateful.
Thanks.
David WalkerShoudl go along with this:
Select O.Quantity,O.ArticleID, SR.Rate
From Orders O
INNER JOIN
(
Select SalesRate.ArticleId,SalesRate.Rate,MAX(SalesLvl)
FROM SalesRate
Where O.Quantity > SalesRate.SalesLvl
Group by SalesRate.ArticleId,SalesRate.Rate
) SR
ON SR.ArticleID = O.ArticleID
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"DWalker" <none@.none.com> schrieb im Newsbeitrag
news:%2393p2MFQFHA.356@.TK2MSFTNGP14.phx.gbl...
> When certain purchases are made, the commission rate depends on the size
> of the purchase. We get data from an external source that shows the
> expected commission rate, depending on the size of the purchase.
> The commission rate/sales level data comes to us in an Excel file, and I
> need to convert it to some appropriate SQL structure. (I don't have any
> DDL yet because I haven't designed the tables.)
> Here's an example of one commission level record that we get from the
> external source:
> ID, SalesLvl1, SalesLvl2, SalesLvl3, SalesLvl4, Rate1, Rate2, Rate3,
> Rate4
> 1X123, 10000, 25000, 50000, 1E14, 0.05, 0.035, 0.025,
> 0.02
> After I turn this into a sensible SQL table, then I'll see a sale of X
> dollars for product ID 1X123, and I need to find the expected commission
> level.
> (Sales from .01 to SalesLvl1 (including = SalesLvl1) get Rate1; sales
> above SalesLvl1 and less than or equal to SalesLvl2 get Rate2, etc. It
> is expected that no sale will be greater than the last level.)
> If I expand the incoming data into four records with the product ID, the
> max sales level for that rate, and the rate, then I would be looking for
> the "first" sales level that's larger than the purchase size, and then
> get the rate. But that implies an ordering on the records, and I know
> that SQL records don't intrinsically have an ordering.
> Do I need to design each row to hold the min and the max sales level for
> that rate, and then search for records where the sales level is between
> that min and max? How can I prevent overlapping records?
> I have searched the Web for SQL and commission, and also sales level,
> and breakpoint (of course searching on SQL and breakpoint gives me
> unrelated stuff), and I can't find examples. If anyone can point me to
> examples of how to do this -- I'm sure it is not a new problem -- I
> would be grateful.
> Thanks.
> David Walker
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'd create a table like this:
CREATE TABLE SalesRates (
product_id char(5) not null ,
lvl int not null CHECK (lvl BETWEEN 1 AND 4) ,
amt_start decimal(17,2) not null , -- scale of 17 'cuz of 1E14 value
amt_end decimal(17,2) not null ,
rate decimal(5,4) not null
CHECK (rate > 0 AND rate < 1) ,
CONSTRAINT PK_SalesRates PRIMARY KEY (product_id, lvl, amt_start) ,
CONSTRAINT CK_InBounds CHECK (amt_start < amt_end AND amt_end >
amt_start)
)
go
-- and a trigger like the following to prevent overlaps:
CREATE TRIGGER NoOverlap ON SalesRates FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
IF (SELECT Count(*)
FROM SalesRates AS R, inserted as i
WHERE i.product_id <> R.product_id
AND (i.amt_start BETWEEN R.amt_start AND R.amt_end
OR i.amt_end BETWEEN R.amt_start AND R.amt_end)) > 0
BEGIN
RAISERROR ('The Amount overlaps existing data',16,1)
ROLLBACK TRANSACTION
END
go
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQl14QYechKqOuFEgEQLkcgCgpHpzXDtfP8rR
sv/RqB8e4CoV46kAn3TA
zKORMNvGlmntLqJGTG+y18VI
=gZYn
--END PGP SIGNATURE--
DWalker wrote:
> When certain purchases are made, the commission rate depends on the size
> of the purchase. We get data from an external source that shows the
> expected commission rate, depending on the size of the purchase.
> The commission rate/sales level data comes to us in an Excel file, and I
> need to convert it to some appropriate SQL structure. (I don't have any
> DDL yet because I haven't designed the tables.)
> Here's an example of one commission level record that we get from the
> external source:
> ID, SalesLvl1, SalesLvl2, SalesLvl3, SalesLvl4, Rate1, Rate2, Rate3,
> Rate4
> 1X123, 10000, 25000, 50000, 1E14, 0.05, 0.035, 0.025,
> 0.02
> After I turn this into a sensible SQL table, then I'll see a sale of X
> dollars for product ID 1X123, and I need to find the expected commission
> level.
> (Sales from .01 to SalesLvl1 (including = SalesLvl1) get Rate1; sales
> above SalesLvl1 and less than or equal to SalesLvl2 get Rate2, etc. It
> is expected that no sale will be greater than the last level.)
> If I expand the incoming data into four records with the product ID, the
> max sales level for that rate, and the rate, then I would be looking for
> the "first" sales level that's larger than the purchase size, and then
> get the rate. But that implies an ordering on the records, and I know
> that SQL records don't intrinsically have an ordering.
> Do I need to design each row to hold the min and the max sales level for
> that rate, and then search for records where the sales level is between
> that min and max? How can I prevent overlapping records?
> I have searched the Web for SQL and commission, and also sales level,
> and breakpoint (of course searching on SQL and breakpoint gives me
> unrelated stuff), and I can't find examples. If anyone can point me to
> examples of how to do this -- I'm sure it is not a new problem -- I
> would be grateful.
> Thanks.
> David Walker
>|||Table structur should go like that (denormlized
ArticleId
SalesLvl
SalesRate
Overlapping could be controlled with a check constaint or a trigger.
Have a nice day,
HTH, Jens Smeyer
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:eAkBDaFQFHA.3144@.tk2msftngp13.phx.gbl...
> Shoudl go along with this:
>
> Select O.Quantity,O.ArticleID, SR.Rate
> From Orders O
> INNER JOIN
> (
> Select SalesRate.ArticleId,SalesRate.Rate,MAX(SalesLvl)
> FROM SalesRate
> Where O.Quantity > SalesRate.SalesLvl
> Group by SalesRate.ArticleId,SalesRate.Rate
> ) SR
> ON SR.ArticleID = O.ArticleID
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "DWalker" <none@.none.com> schrieb im Newsbeitrag
> news:%2393p2MFQFHA.356@.TK2MSFTNGP14.phx.gbl...
>|||Great, thanks to everyone who answered. It helps!
David
MGFoster <me@.privacy.com> wrote in
news:iMe7e.5726$lP1.2917@.newsread1.news.pas.earthlink.net:

> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
>
> I'd create a table like this:
> CREATE TABLE SalesRates (
> product_id char(5) not null ,
> lvl int not null CHECK (lvl BETWEEN 1 AND 4) ,
> amt_start decimal(17,2) not null , -- scale of 17 'cuz of 1E14
> value amt_end decimal(17,2) not null ,
> rate decimal(5,4) not null
> CHECK (rate > 0 AND rate < 1) ,
> CONSTRAINT PK_SalesRates PRIMARY KEY (product_id, lvl, amt_start) ,
> CONSTRAINT CK_InBounds CHECK (amt_start < amt_end AND amt_end >
> amt_start)
> )
> go
> -- and a trigger like the following to prevent overlaps:
> CREATE TRIGGER NoOverlap ON SalesRates FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT = 0
> RETURN
> IF (SELECT Count(*)
> FROM SalesRates AS R, inserted as i
> WHERE i.product_id <> R.product_id
> AND (i.amt_start BETWEEN R.amt_start AND R.amt_end
> OR i.amt_end BETWEEN R.amt_start AND R.amt_end)) > 0
> BEGIN
> RAISERROR ('The Amount overlaps existing data',16,1)
> ROLLBACK TRANSACTION
> END
> go|||Thanks, Jens.
David
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in news:OyD7COGQFHA.1340@.TK2MSFTNGP10.phx.gbl:

> Table structur should go like that (denormlized
> ArticleId
> SalesLvl
> SalesRate
> Overlapping could be controlled with a check constaint or a trigger.
> Have a nice day,
> HTH, Jens Smeyer
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> schrieb im Newsbeitrag news:eAkBDaFQFHA.3144@.tk2msftngp13.phx.gbl...
>