Q

Splitting number ranges

I am having hard time trying to split number ranges. The following three examples would help explain better. If we can do this in SQL it would be great, if not, a procedure or function would also be fine. I have tried both, but cannot seem to handle all the cases as described below. Could you please help me?

The table with two columns, serial_start and serial_end, has the following data:

SERIAL_START   SERIAL_END
-------------  ---------- 
40             140
60             145
100            110
150            250

User Input: Serial_Start = 30, Serial_End = 40

Expected Output: 30-39 
When a user inputs a serial_begin=30 and serial_end=40, I have to look into this table and say, OK, the only serials that can be assigned are 30 - 39. This is because 40 is already used in the table.

Another example: Same table has the following data:

SERIAL_START   SERIAL_END
-------------  ---------- 
200            230

User Input: Serial_Start = 150, Serial_End = 250

Expected Output: 150 - 199
                 231 - 250  
When a user inputs a serial_begin=150 and serial_end=250, I have to look into this table and say, OK, the only serials that can be assigned are 150 - 199 and 231 - 250. This is because 200 - 230 are already used in the table.

Another example: Same table has the following data:

SERIAL_START   SERIAL_END
-------------  ---------- 
50             98
100            150
160            190
200            230

User Input: Serial_Start = 90, Serial_End = 120

Expected Output: 99 - 99


Looks like you also submitted this question to the "Ask Tom" site and he beat us to the answer. For anyone who's interested, you can find the solution to this question here.

For More Information


This was first published in February 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close