May 9, 2007 at 7:59 am
I've got a procedure that I'd like to change to being more of a set based rather than a row based solution.
What I do is process approx 2000 rows worth of data at any given week. These rows need to be updated, however the update is relatively dynamic.
What I need is the following:
I've got a table called Points. Inside it has values for the following:
DollarLow,DollarHigh,PointLow,PointHigh.
What I need to update is a record in a Base table that has a Dollar Per Hour calculation that falls within the DollarLow and DollarHigh and is then assigned a corresponding Point value based on how much over the Low value it is.
For example:
DollarLow=1, DollarHigh=10,PointLow=1,PointHigh=10
If a run a calculation that resolves to $6 / Hour, then I need to be able to assign it 6 points.
(Needless to say, none of my results are this straightforward, the largest majority of them are much smaller Ranges)
Currently, I run a stored proc that parses out alot of information for it to then be able to look up the data, and then it calculates the difference between the highs and lows and associates a point value based on how much above the low the agent did.
This stored proc takes ~2-3 minutes to run because it's written so inefficiently. But it only runs twice per week, so overall efficiency really isn't a big issue at this point, other than the fact that I'd like to find a solution that worked so I can integrate it into future projects (after I have refactored this one as well).
TIA,
-Terry Gamble
May 9, 2007 at 9:00 am
I don't think you gave me enough information to solve the problem. I'm giving it a stab but making a lot of assumptions that probably are not correct.
Assumptions:
1. No duplicate or overlapping ranges in the "Points" table.
2. Base table with the $6/hour has the $6/hour calculated elsewhere.
3. I'm ignoring the high/low point values since you did not explain how the fit in the equation
4. Your point value of 6 for $6 being that high over the low is hard to interpet as the low is 1 and 6
is only 5 over that value. I'm going with the 5 to keep the calculations simple (you can adjust as needed)
Base on all that, here is a simple solution, based on database objects built from what you provided.
(Hint: In the future provide table schemas and limited test data)
if exists (select 1 from sysobjects where id = object_id('points_') and type = 'U') drop table Points_
go
if exists (select 1 from sysobjects where id = object_id('test_') and type = 'U') drop table test_
go
create table points_ (
dollarLow_ int,
dollarhigh_ int,
pointlow_ int,
pointHigh_ int
)
go
insert into points_ values (1,10,1,10)
insert into points_ values (11,20,1,10)
insert into points_ values (21,30,1,10)
insert into points_ values (31,70,1,40) --40 max points (actually 39) since that is the max amount over the min?
go
create table test_(
id_ numeric(10,0) not null identity,
perHourAmount_ int,
points_ int,
constraint pk_test_ primary key (id_)
)
go
insert test_ values (6,0) --gets a point value of 5?
insert test_ values (12,-1) --gets a point value of 1? (except this used as baseline to insure only records with points_ = 0 are updated)
insert test_ values (23,0) --gets a point value of 2?
insert test_ values (64,0) --gets a point value of 33?
go
select * from points_
go
select * from test_
go
-----------------------------------------------------------------------------------------------------------------
--** Here is the solution you were looking for, if all my assumptions were correct (which I'm sure is unlikely)
-----------------------------------------------------------------------------------------------------------------
--Update statement that will identify all records without points and update
update test_
set points_ = (perHourAmount_ - dollarLow_)
from test_ t join points_ p on (t.perHourAmount_ between p.dollarLow_ and p.dollarHigh_)
where points_ = 0
go
select * from test_
go
/*------------------ALTERNATE SOLUTION-------------------------------
--You could consider a simple trigger on the base table that will
--calculate the "point" value each time a recod is inserted/updated
--this has the advantage of "fixing" the points if manual updates are done
---------------------------------------------------------------------*/
Create trigger tr_test_IU_ on test_ for Insert,Update as
begin
if UPDATE(perHourAmount_)
update test_
set points_ = (i.perHourAmount_ - p.dollarLow_)
from test_ t join inserted i on (t.id_ = i.id_)
join points_ p on (i.perHourAmount_ between p.dollarLow_ and p.dollarHigh_)
end
go
--repeat our insert values, now using the trigger to calculate the points
insert test_ values (6,0) --gets a point value of 5?
insert test_ values (12,-1) --gets a point value of 1? (except this used as baseline to insure only records with points_ = 0 are updated)
insert test_ values (23,0) --gets a point value of 2?
insert test_ values (64,0) --gets a point value of 33?
go
--Compare the results
select * from test_
May 9, 2007 at 10:09 am
Modified your sample and added a scenario in the comments. I didn't change the way you're calculating because that's the piece I'm having trouble logically thinking through as far as the solution. I have modified the provided example to reflect some base values however.
if exists (select 1 from sysobjects where id = object_id('points_') and type = 'U') drop table Points_
go
if
exists (select 1 from sysobjects where id = object_id('test_') and type = 'U') drop table test_
go
create
table points_ (
dollarLow_
NUMERIC(10,2),
dollarhigh_
NUMERIC(10,2),
pointlow_
NUMERIC(10,2),
pointHigh_
NUMERIC(10,2)
)
GO
insert
into points_ values (0,4.75,0,.99)
insert
into points_ values (4.76,9.78,1,1.99)
insert
into points_ values (9.79,11.14,2,2.99)
go
create
table test_(
id_
numeric(10,0) not null identity,
perHourAmount_
NUMERIC(10,2),
points_
NUMERIC(10,2),
constraint pk_test_ primary key (id_)
)
go
insert
test_ values (6,0) --gets a point value of 5?
insert
test_ values (4.3,-1) --gets a point value of 1? (except this used as baseline to insure only records with points_ = 0 are updated)
insert
test_ values (2.1,0) --gets a point value of 2?
insert
test_ values (.7,0) --gets a point value of 33?
go
select
* from points_
go
select
* from test_
go
-----------------------------------------------------------------------------------------------------------------
--** Here is the solution you were looking for, if all my assumptions were correct (which I'm sure is unlikely)
-----------------------------------------------------------------------------------------------------------------
--Update statement that will identify all records without points and update
update
test_
set points_ = (perHourAmount_ - dollarLow_)
from test_ t join points_ p on (t.perHourAmount_ between p.dollarLow_ and p.dollarHigh_)
where points_ = 0
go
select
* from test_
go
/* Providing More Information
Note : These calculations are very "easy" in that they are based off a scale of 1:10. However in my normal data, the values are 0-4.75 and 4.76-9.78 and 9.79-11.14.
IE for the values it would be:
$Low $High PtLow PtHIGH
0 4.75 0 .99
4.76 9.78 1 1.99
9.79 11.14 2 2.99
Which would mean for our two examples the "value" would be:
Start with $/Hr at 6
That would fall between 4.76 and 9.78.
DollarHigh-6= 3.78
DollarHigh-DollarLow= 5.02
Thus 3.78/5.02 = 75% (rounding to whole numbers for ease. In my database I go out to 4 decimal places total. IE 75.30%)
Therefore my agent would get 75% of the difference between 1.99 and 1 added to his low amt.
IE 1.75 PPH.
*/
May 9, 2007 at 10:47 am
Ok, I altered the "test_" table to contain an additional column which will hold the amount calculated for the agent, along with and extra column to hold the "low dollar amount" used in the calculation (helps verify the UPDATE sql is using the correct points_ record) and updated the final "UPDATE" statement calculation to reflect what you told me in the additional information. Please check and see if the calculations bear up against test data with KNOWN results.
if exists (select 1 from sysobjects where id = object_id('points_') and type = 'U') drop table Points_
go
if exists (select 1 from sysobjects where id = object_id('test_') and type = 'U') drop table test_
go
create table points_ (
dollarLow_ NUMERIC(10,2),
dollarhigh_ NUMERIC(10,2),
pointlow_ NUMERIC(10,2),
pointHigh_ NUMERIC(10,2)
)
go
insert into points_ values (0,4.75,0,.99)
insert into points_ values (4.76,9.78,1,1.99)
insert into points_ values (9.79,11.14,2,2.99)
go
-- *** Adjusted the test_ table to include a column to hold the resulting amount the agent should get
-- along with the "low dollar amount" used in the calculation so we can verify that the SQL is
-- using the correct record from the points_ table.
create table test_(
id_ numeric(10,0) not null identity,
perHourAmount_ NUMERIC(10,2),
points_ NUMERIC(10,2),
lowAmount_ numeric(10,2),
agentAmount_ numeric(10,2)
constraint pk_test_ primary key (id_)
)
go
--NOTE: Becuase this is a test, it would be helpful to manually calcualte the results so
-- we can verify the output. Based on your comments my calcs result in the following
insert test_ (perHourAmount_) values (6) --Shoud result in 0.75 points and agentAmout = 5.51?
insert test_ (perHourAmount_) values (4.3) --Shoud result in .09 and .09?
insert test_ (perHourAmount_) values (2.1) --Shoud result in .56 and .55?
insert test_ (perHourAmount_) values (.7) --Shoud result in .85 and .84?
go
select * from points_
go
select * from test_
go
-----------------------------------------------------------------------------------------------------------------
--** Here is the solution you were looking for, if all my assumptions were correct (which I'm sure is unlikely)
-----------------------------------------------------------------------------------------------------------------
--Update statement that will identify all records without points and update
update test_
set points_ = ((p.dollarhigh_ - perHourAmount_) / (p.dollarHigh_ - p.dollarLow_)),
lowAmount_ = p.dollarLow_,
agentAmount_ = ((((p.dollarhigh_ - perHourAmount_) / (p.dollarHigh_ - p.dollarLow_)) * (p.pointHigh_ - p.pointLow_)) + p.dollarLow_)
from test_ t join points_ p on (t.perHourAmount_ between p.dollarLow_ and p.dollarHigh_)
where points_ is null
go
select * from test_
go
/* RESULTS:
id_ perHourAmount_ points_ lowAmount_ agentAmount_
--------- ------------------- --------- ------------------- -------------------
1 6.00 0.75 4.76 5.51
2 4.30 0.09 0.00 0.09
3 2.10 0.56 0.00 0.55
4 0.70 0.85 0.00 0.84
*/
/* Providing More Information
Note : These calculations are very "easy" in that they are based off a scale of 1:10. However in my normal data, the values are 0-4.75 and 4.76-9.78 and 9.79-11.14.
IE for the values it would be:
$Low $High PtLow PtHIGH
0 4.75 0 .99
4.76 9.78 1 1.99
9.79 11.14 2 2.99
Which would mean for our two examples the "value" would be:
Start with $/Hr at 6
That would fall between 4.76 and 9.78.
DollarHigh-6= 3.78
DollarHigh-DollarLow= 5.02
Thus 3.78/5.02 = 75% (rounding to whole numbers for ease. In my database I go out to 4 decimal places total. IE 75.30%)
Therefore my agent would get 75% of the difference between 1.99 and 1 added to his low amt.
IE 1.75 PPH.
*/
May 9, 2007 at 11:55 am
I think this might do it.
I'm working on modifying the update slightly (pointlow_ needed to be added to the .75 instead of the dollarlow_) and working it into integrating with my test data to make sure that the values all come out the exact same.
Thanks greatly for the help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply