asked on

Missing number in a sequence of numbers

i have a table that i would like to know the missing numbers or a range of missing numbers if more than 1 was skipped

i.e

4
7
12-14
20

this sql gives me my list of numbers

Select distinct substring(policy_number,8,6) FROM dbo.tPolicyWhse
WHERE (policy_number LIKE '87a2im%') and (recordtype = 0)
Order BY substring(policy_number,8,6)

Deleted by Vee_Mod, no points refunded: 10/28/2009 9:27:27 AM

sgomzin

You need somthing like this:
* Create stored procedure
* Create temp table for results
* Create cursor based on your quesry nad loop through the records
* On each iteration, compare current and previous records and if there is a gap - write the number (or numbers range) representing the gap into the temp table as a new record
* select * from temp table

19420201

ASKER

sgomzin
i know what i need to do
i asked experts-exchange how do do it
if i'd known how do do it why would i ask the question?

sgomzin

OK, I understand. In most cases algorithm is what people are looking for, because implementation is usually easier part. Let me see if I can help you in timely manner.

ASKER CERTIFIED SOLUTION

sgomzin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

19420201

ASKER

acperkins
that's fine
he or she might might be a real expert

19420201

ASKER

acperkins what happened to mine and your dialogue on this question

is it possible that
experts exchange does not want the user community to see the dialogue between experts like you

19420201

ASKER

vee_mod

if you notice in my posting
i asked acperkins that we needed to end the dialogue, it didn't happened
please pass the word along
as far as I'm concerned the question is complete and closed