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

Normal view compare to partitioned view

Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florence
Hi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:

> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>

Non-SysAdmins have been denied permission to run CmdExec job steps

Is there a certain role or permission that a user must be given to run jobs?
Thanks.
Ron
Per BOL, under "jobs, running/ Transact SQL".
Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role can
start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
ChrisR
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron
|||Under properties of SQL Server Agent there's a setting under the job system
tab:
"Non-sysadmin job step proxy account" - I checked this off and users were OK.
Ron
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron

Non-SysAdmins have been denied permission to run CmdExec job steps

Is there a certain role or permission that a user must be given to run jobs?
Thanks.
RonPer BOL, under "jobs, running/ Transact SQL".
Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role can
start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
--
ChrisR
"Ron" wrote:
> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron|||Under properties of SQL Server Agent there's a setting under the job system
tab:
"Non-sysadmin job step proxy account" - I checked this off and users were OK.
Ron
"Ron" wrote:
> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron

Non-SysAdmins have been denied permission to run CmdExec job steps

Is there a certain role or permission that a user must be given to run jobs?
Thanks.
RonPer BOL, under "jobs, running/ Transact SQL".
Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role ca
n
start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
ChrisR
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run job
s?
> Thanks.
> Ron|||Under properties of SQL Server Agent there's a setting under the job system
tab:
"Non-sysadmin job step proxy account" - I checked this off and users were OK
.
Ron
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run job
s?
> Thanks.
> Ronsql

Noise words issue in full text seach Sql server 2000

Hi All,

I have been using full text search in my application for search purpose.
But on certain words i.e Noise Words such as And,After,About etc.
I am receiving the following error:

Execution of a full-text operation failed. A clause of the query contained only ignored words.

I tried a lot to sort out this issue,but this is going to be out of my scope.
If anybody can help me then it would be really appreciable.

Thanks in Advance,
Vivek RathoreFull-text maintains a list of noise words in different files which are language specific. When you receive the Msg 7619 The query contained only ignored words" SQL server has eliminated all words in the query. If you would like to search on those words you can try utilizing FREETEXT or editing the files maintaining the list of noise words.

These noise-word lists should be sufficient for most normal operations, but can be modified for specific environments with a text editor. For more information, see the Indexing Service 2.0 documentation in the Windows NT 4.0 Option Pack.