Storing ranges in a table - SQL Server Forums

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

SQL Server Development (2000)

Storing ranges in a table

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-05-25 : 17:58:20

I work for a telemarketing company. They pay their reps based on the rep's total calltimes.
I have the following payrates that I need stored in a table:

Level PayRate Range
1 $6.15 1st 20 hours of dialing
2 $8.50 From 21st hour of dialing to the 90th day of
dialing
3 $10 From the 91st day of dailing

How can I break up the ranges into a table? I will have the rep's total calltime and based on that I need to figure out the payrate level.

Thanks,
Ninel

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-25 : 19:25:08

Do I read this right that in level 2 you switch from tracking hours to tracking days? What is the base unit for measuring call times? I'm thinking an integer (BigInt?) that matches the base unit of call times.

---------------------------
EmeraldCityDomains.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-25 : 21:20:40

>>I will have the rep's total calltime
In what format will you have the total calltime?

Here is one possible solution where totalcalltime and range values are all in Hours:


set nocount on
declare @rates table (lowRange int, hiRange int, rate money)
insert @rates 
select 0,20,6.15 union
select 21, 2160, 8.50 union
select 2161, 10000000, 10 

declare @reps table (repid int, totalCallTime int)
insert @reps
select 1, 11 union
select 2, 18 union
select 3, 45 union
select 4, 6002 union
select 5, 2160


select	repid
	,rate
from	@reps a
join	@rates b
		on a.totalcalltime between b.lowRange and b.hiRange

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources