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...
>

Normalization

can i get little idea about normalization?
where i will found all levels of normalization along with
example?
Thanks and regards
hrishikeshPerhaps you should read some books about Datadase design (also included
clustered and non clustered indexes) :))
http://www.amazon.de/exec/obidos/ASIN/0735619603/sqlserver-21
Or
http://www.amazon.de/exec/obidos/ASIN/1861004761/sqlserver-21
(Wrox press books are very good)
These are sql server specific books, but also capable for other RDBMS (eg.
Oracle)
HTH, Jens Süßmeyer.|||look at this site.
http://www.sqlserversite.com/content/articles/20020418.asp --Normalizat
ion database
"hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
news:03f901c36d55$af8395f0$a001280a@.phx.gbl...
> can i get little idea about normalization?
> where i will found all levels of normalization along with
> example?
>
> Thanks and regards
> hrishikesh|||Can look at http://www.extremeexperts.com/sql/articles/Normalizing.aspx
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
news:03f901c36d55$af8395f0$a001280a@.phx.gbl...
> can i get little idea about normalization?
> where i will found all levels of normalization along with
> example?
>
> Thanks and regards
> hrishikesh

Non-Homogenous Hierachy

I have a request to make a hierarchy where by each level in the tree is not
from the same table.
Originally it was ok the first 3 levels where of the same table so I simply
added a ParentID field and make a self referencing foreign key to the table.
However, after the 3rd level the items come from 3 other tables.
Categories Table
Level1
Level2
Level3
Other Table1
Level4
Other Table2
Level5
The linkage is ok but I need to generate/populate a treeview object from
this. Forming the tree is a bit awkward as when a node is selected I need
to set in the database that the child nodes are selected. Normally I could
use a CTE expression to traverse the tree but depending on what level you
select you need to traverse different tables which makes the statement
difficult.
I'd love to find a way to represent the hierarchy in a single table so I can
do parent/child ID lookups in the same table.
Any thoughts/suggestions really appreciated.
TIA
MattCOn Jun 14, 12:02 pm, "MattC" <m...@.m.com> wrote:
> I have a request to make a hierarchy where by each level in the tree is not
> from the same table.
> Originally it was ok the first 3 levels where of the same table so I simply
> added a ParentID field and make a self referencing foreign key to the table.
> However, after the 3rd level the items come from 3 other tables.
> Categories Table
> Level1
> Level2
> Level3
> Other Table1
> Level4
> Other Table2
> Level5
> The linkage is ok but I need to generate/populate a treeview object from
> this. Forming the tree is a bit awkward as when a node is selected I need
> to set in the database that the child nodes are selected. Normally I could
> use a CTE expression to traverse the tree but depending on what level you
> select you need to traverse different tables which makes the statement
> difficult.
> I'd love to find a way to represent the hierarchy in a single table so I can
> do parent/child ID lookups in the same table.
> Any thoughts/suggestions really appreciated.
> TIA
> MattC
Hi
Could you not create a view that Unions the three tables? There is no
information on the table structures (DDL), example data or what you
are returning to the treeview!! (see http://www.aspfaq.com/etiquette.asp?id=5006)
A different approach may be to use XML and the ability in SQL 2005 to
have nested statements.
John

Non-Homogenous Hierachy

I have a request to make a hierarchy where by each level in the tree is not
from the same table.
Originally it was ok the first 3 levels where of the same table so I simply
added a ParentID field and make a self referencing foreign key to the table.
However, after the 3rd level the items come from 3 other tables.
Categories Table
Level1
Level2
Level3
Other Table1
Level4
Other Table2
Level5
The linkage is ok but I need to generate/populate a treeview object from
this. Forming the tree is a bit awkward as when a node is selected I need
to set in the database that the child nodes are selected. Normally I could
use a CTE expression to traverse the tree but depending on what level you
select you need to traverse different tables which makes the statement
difficult.
I'd love to find a way to represent the hierarchy in a single table so I can
do parent/child ID lookups in the same table.
Any thoughts/suggestions really appreciated.
TIA
MattCOn Jun 14, 12:02 pm, "MattC" <m...@.m.com> wrote:
> I have a request to make a hierarchy where by each level in the tree is no
t
> from the same table.
> Originally it was ok the first 3 levels where of the same table so I simpl
y
> added a ParentID field and make a self referencing foreign key to the tabl
e.
> However, after the 3rd level the items come from 3 other tables.
> Categories Table
> Level1
> Level2
> Level3
> Other Table1
> Level4
> Other Table2
> Level5
> The linkage is ok but I need to generate/populate a treeview object from
> this. Forming the tree is a bit awkward as when a node is selected I need
> to set in the database that the child nodes are selected. Normally I coul
d
> use a CTE expression to traverse the tree but depending on what level you
> select you need to traverse different tables which makes the statement
> difficult.
> I'd love to find a way to represent the hierarchy in a single table so I c
an
> do parent/child ID lookups in the same table.
> Any thoughts/suggestions really appreciated.
> TIA
> MattC
Hi
Could you not create a view that Unions the three tables? There is no
information on the table structures (DDL), example data or what you
are returning to the treeview!! (see [url]http://www.aspfaq.com/etiquette.asp?id=5006[/
url])
A different approach may be to use XML and the ability in SQL 2005 to
have nested statements.
John