April 3, 2008 at 11:06 am
I have a table carrying ranges like shown below
BEGIN_RNG END_RNG
42339130004233913999
35298990003529899999
35220580003522058999
35220570003522057999
35253660003525366999
35275170003527517999
35275160003527516999
35275150003527515999
35275140003527514999
70226750007022675999
I have another table carrying emp_numbers
4108996789
4233913010
4356187920
3527514089
7022675098
etc..
I have to find out which of these emp_numbers fall into any of the ranges defined in the table above.
I will prefer not use a CURSOR logic.
Thanks
April 3, 2008 at 12:59 pm
This what I have come up with let me know if you see an issue with this or a way to make it efficient.
select b.emp_nbr, begin_rng, end_rng
from range a
join emp b
on 1 = 1
where b.emp_nbr between a.begin_rng and a.end_rng
April 3, 2008 at 1:09 pm
Use an actual join condition in your join:
select
b.emp_nbr,
a.begin_rng,
a.end_rng
from
range a
join
emp b
on b.emp_nbr between a.begin_rng and a.end_rng
April 3, 2008 at 1:09 pm
There's really no reason you can't do it directly in the ON statement:
select b.emp_nbr, begin_rng, end_rng
from range a
join emp b
ON b.emp_nbr between a.begin_rng and a.end_rng
It's obviously not an equijoin, but it certainly is valid. With the right indexing, also should not be too bad performance-wise either.
I don't know if it's just your test data, but there's a definite pattern, which you could leverage into an equijoin. In other words: since you're breaking things up by 10000's, that would be something easy to put into a computed column on the emp table, which then becomes easy to index.
for example - if you were to create a computed column called EmpHomeRange with the following formula:
emp_number - (empnumber % 10000)
then empHomeRange would match up with the BEGIN_RNG from your range table, thus becoming an EQUIJOIN.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 1:26 pm
Here is some test code I threw together. Take it and play!
create table #MyTable1 (
BeginRange decimal(18,0),
EndRange decimal(18,0),
RangeDescription varchar(10)
)
create table #MyTable2 (
EmployeeNumber decimal(18,0),
EmployeeName varchar(20)
)
insert into #MyTable1 (BeginRange, EndRange, RangeDescription)
select 4233913000,4233913999,'Range 1' union
select 3529899000,3529899999,'Range 2' union
select 3522058000,3522058999,'Range 3' union
select 3522057000,3522057999,'Range 4' union
select 3525366000,3525366999,'Range 5' union
select 3527517000,3527517999,'Range 6' union
select 3527516000,3527516999,'Range 7' union
select 3527515000,3527515999,'Range 8' union
select 3527514000,3527514999,'Range 9' union
select 7022675000,7022675999,'Range 10'
insert into #MyTable2 (EmployeeNumber, EmployeeName)
select 4108996789,'Employee 1' union
select 4233913010,'Employee 2' union
select 4356187920,'Employee 3' union
select 3527514089,'Employee 4' union
select 7022675098,'Employee 5'
select
mt2.EmployeeNumber,
mt1.BeginRange,
mt1.EndRange,
mt1.RangeDescription,
mt2.EmployeeName
from
#MyTable1 mt1
inner join #MyTable2 mt2
on (mt2.EmployeeNumber between mt1.BeginRange and mt1.EndRange)
select
mt2.EmployeeNumber,
mt1.BeginRange,
mt1.EndRange,
mt1.RangeDescription,
mt2.EmployeeName
from
#MyTable1 mt1
full outer join #MyTable2 mt2
on (mt2.EmployeeNumber between mt1.BeginRange and mt1.EndRange)
drop table #MyTable1
drop table #MyTable2
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply