CODEWITHSUNDEEP
sqlt-sqlloopsbetween
Charles Bernardes
Charles Bernardes

Reputation: 313

Find if phonenumbers exist between 2 columns

I am trying to pass two 10 digit telephone number block using TSQL.

Lets say:

TelephoneNumber1: 1234560095
TelephoneNumber2: 1234561005

My Table consists of 3 columns: 

ID, StartBlock, EndBlock
1, 5671231000, 5671232000
2, 1234561000, 1234562000
3, 2175551200, 2175551300

So what I am trying to do is find all the numbers between TelephoneNumber1 and TelephoneNumber2 and see if it exists between any records between StartBlock and EndBlock in the table.

In this example, it would find a number already in use in the second record because when 1234560095 gets to 1234561000, it will find it in already in use.

How do I accomplish this to find a number in use between the StartBlock and EndBlock?

Upvotes: 5

Views: 996

Answers (4)

Mike H-R
Mike H-R

Reputation: 7825

I'm not sure if I'm missing something but wouldn't it just be:

SELECT * from MYTABLE 
WHERE (StartBlock=>TelehoneNumber1 AND StartBlock<=TelephoneNumber2) 
OR (EndBlock=>TelehoneNumber1 AND EndBlock<=TelephoneNumber2)

EDIT: just looked at this and saw BETWEEN, another option: http://www.w3schools.com/sql/sql_where.asp

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

declare @T table
(
  ID int,
  StartBlock bigint, 
  EndBlock bigint
)

insert into @T values
(1, 5671231000, 5671232000),
(2, 1234561000, 1234562000),
(3, 2175551200, 2175551300)

declare @TelephoneNumber1 bigint 
declare @TelephoneNumber2 bigint 

set @TelephoneNumber1 = 1234560095
set @TelephoneNumber2 = 1234561005

select * 
from @T
where StartBlock <= @TelephoneNumber2 and 
      EndBlock >= @TelephoneNumber1

Upvotes: 2

Karey Powell
Karey Powell

Reputation: 482

This can be accomplished by simply doing:

SELECT * FROM tblName
WHERE StartBlock >= '5671231000' AND EndBlock <= '5671232000'

Result:

| ID | StartBlock | EndBlock |
--------------------------------
| 1  | 5671231000 | 5671232000 |
--------------------------------
| 4  | 5671231120 | 5671231010 |
--------------------------------
| 5  | 5671231540 | 5671231999 |

It should produce a result with the start and end block, including everything that falls between. Have a look at these TSQL example where you will find two ways how to accomplish this.

Demo here.

Upvotes: 1

Kermit
Kermit

Reputation: 34063

Is this what you're looking for?

SELECT * FROM tel
WHERE StartBlock <= '1234561005' AND EndBlock >= '1234561005'

Result

| ID | STARTBLOCK |   ENDBLOCK |
--------------------------------
|  2 | 1234561000 | 1235552000 |

See a demo

Upvotes: 1

Related Questions