asked on # T-SQL showing missing document numbers

Hi

Need some help with a query to display missing document number. Field1 is a sequence number, how can I return the numbers that are missing from Field1?

Need some help with a query to display missing document number. Field1 is a sequence number, how can I return the numbers that are missing from Field1?

Microsoft SQL ServerSSRSSQL

You could do a LAG function, and where the LAG is more than one, you have your gap.

This may have the added advantage of grouping together the gaps, so that numbers 10-20 are missing, you will see a GAP from 10-20, instead of a Gap in 10, 11, 12, 13, 14 etc.

This may have the added advantage of grouping together the gaps, so that numbers 10-20 are missing, you will see a GAP from 10-20, instead of a Gap in 10, 11, 12, 13, 14 etc.

;with cte(row_num) as

(select min(field1) as row_num from yourtable

union all

select row_num + 1 from cte

where row_num < (select max(field1) from yourtable)

)

select * from cte

except

select field1 from yourtable;

(select min(field1) as row_num from yourtable

union all

select row_num + 1 from cte

where row_num < (select max(field1) from yourtable)

)

select * from cte

except

select field1 from yourtable;

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

@ Shaun :

I added the required table info to the query.

";WITH e1(n) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), -- 10

e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10

e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100

e4(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM e3 )

SELECT e4.n

FROM e4

LEFT OUTER JOIN Files ON e4.n = Files.Field1

WHERE Field1 IS NULL

ORDER BY n;"

Got the following reply.

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to bigint.

Field1 is a varchar(255), how can I convert it?

I added the required table info to the query.

";WITH e1(n) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), -- 10

e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10

e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100

e4(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM e3 )

SELECT e4.n

FROM e4

LEFT OUTER JOIN Files ON e4.n = Files.Field1

WHERE Field1 IS NULL

ORDER BY n;"

Got the following reply.

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to bigint.

Field1 is a varchar(255), how can I convert it?

CAST(Field1 AS BIGINT)

;with cte(row_num) as

(select min(cast(field1 as bigint) as row_num from yourtable

union all

select row_num + 1 from cte

where row_num < (select max(cast(field1 as bigint) from yourtable)

)

select * from cte

except

select cast(field1 as bigint) from yourtable;

(select min(cast(field1 as bigint) as row_num from yourtable

union all

select row_num + 1 from cte

where row_num < (select max(cast(field1 as bigint) from yourtable)

)

select * from cte

except

select cast(field1 as bigint) from yourtable;

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Hi Awking

Tried the query as follows.

;with cte(row_num) as

(select min(cast(field1 as bigint) as row_num from Files

union all

select row_num + 1 from cte

where row_num < (select max(cast(field1 as bigint) from Files)

)

select * from cte

except

select cast(field1 as bigint) from Files;

This error is displaying when you hover over line 2.

'min' is not a recognized built in function name.

Also get the following errors when try to execute query.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'from'.

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'from'.

Tried the query as follows.

;with cte(row_num) as

(select min(cast(field1 as bigint) as row_num from Files

union all

select row_num + 1 from cte

where row_num < (select max(cast(field1 as bigint) from Files)

)

select * from cte

except

select cast(field1 as bigint) from Files;

This error is displaying when you hover over line 2.

'min' is not a recognized built in function name.

Also get the following errors when try to execute query.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'from'.

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'from'.

Sorry, left out a parenthesis -

min(cast(field1 as bigint)**)** ==> line 2

max(cast(field1 as bigint)**)** ==> line 5

min(cast(field1 as bigint)

max(cast(field1 as bigint)

please see the solution here

```
DECLARE @StartNo BIgInt = 1;
DECLARE @EndNo BigInt = 5000;
with
x as (
select Field1, row_number() over (order by Field1) rn
from MyTable t
where Field1>= @StartNo and Field1<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
```

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Awking.

Changed the query to the following.

;with cte(row_num) as

(select min(cast(field1 as bigint)) as row_num from Files

union all

select row_num + 1 from cte

where row_num < (select max(cast(field1 as bigint)) from Files)

)

select * from cte

except

select cast(field1 as bigint) from Files;

Got this error.

sg 467, Level 16, State 1, Line 1

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'cte'.

Changed the query to the following.

;with cte(row_num) as

(select min(cast(field1 as bigint)) as row_num from Files

union all

select row_num + 1 from cte

where row_num < (select max(cast(field1 as bigint)) from Files)

)

select * from cte

except

select cast(field1 as bigint) from Files;

Got this error.

sg 467, Level 16, State 1, Line 1

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'cte'.

@Hainkurt

This is your query that I tried.

DECLARE @StartNo BIgInt = 1;

DECLARE @EndNo BigInt = 5000;

with

x as (

select Field1, row_number() over (order by Field1) rn

from Files t

where Field1>= @StartNo and Field1<= @EndNo

)

select x1.Field1+1 gap_start, x2.Field1-1 gap_end

from x x1 inner join x x2 on x1.rn=x2.rn-1

where x1.Field1!= x2.Field1-1

Got the following error.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

This is your query that I tried.

DECLARE @StartNo BIgInt = 1;

DECLARE @EndNo BigInt = 5000;

with

x as (

select Field1, row_number() over (order by Field1) rn

from Files t

where Field1>= @StartNo and Field1<= @EndNo

)

select x1.Field1+1 gap_start, x2.Field1-1 gap_end

from x x1 inner join x x2 on x1.rn=x2.rn-1

where x1.Field1!= x2.Field1-1

Got the following error.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint

what is Field1 datatype? it cannot produce this message if Field1 is int or bigint or whatever int...

```
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
Files as (select 121 Field1 union select 122 union select 125 union select 132),
x as (
select Field1, row_number() over (order by Field1) rn
from Files t
where Field1>= @StartNo and Field1<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
gap_start gap_end
123 124
126 131
```

above is with a sample data... working fine...

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Hi HainKurt

Field1 is a varchar(255)

Field1 is a varchar(255)

actually I should write an article to find a gap...

this is generic solution... that finds gaps over IdNo in MyTable... for a given interval... determined by StartNo & EndNo

this is generic solution... that finds gaps over IdNo in MyTable... for a given interval... determined by StartNo & EndNo

```
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select IdNo, row_number() over (order by IdNo) rn
from MyTable t
where IdNo>= @StartNo and IdNo<= @EndNo
)
select x1.IdNo+1 gap_start, x2.IdNo-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.IdNo!= x2.IdNo-1
```

Field1 is a varchar(255)

you said sequence number in first post... anyways, just use:

Field1 >>>>> cast (Field1 as bigint)

```
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select Cast(Field1 as bigint) Field1, row_number() over (order by Field1) rn
from Files t
where Cast(Field1 as bigint)>= @StartNo and Cast(Field1 as bigint)<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
```

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

@Hainkurt

I made the following changes to the query.

DECLARE @StartNo BIgInt = 100;

DECLARE @EndNo BigInt = 200;

with

x as (

select Field1, row_number() over (order by field1) rn

from Files t

where Field1>= @StartNo and Field1<= @EndNo

)

select x1.Field1+1 gap_start, x2.Field1-1 gap_end

from x x1 inner join x x2 on x1.rn=x2.rn-1

where x1.Field1!= x2.Field1-1

Still getting this error.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

Am I doing something wrong?

I made the following changes to the query.

DECLARE @StartNo BIgInt = 100;

DECLARE @EndNo BigInt = 200;

with

x as (

select Field1, row_number() over (order by field1) rn

from Files t

where Field1>= @StartNo and Field1<= @EndNo

)

select x1.Field1+1 gap_start, x2.Field1-1 gap_end

from x x1 inner join x x2 on x1.rn=x2.rn-1

where x1.Field1!= x2.Field1-1

Still getting this error.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

Am I doing something wrong?

i don't see your changes!

Hi

I added our table name to the query.

DECLARE @StartNo BIgInt = 100;

DECLARE @EndNo BigInt = 200;

with

x as (

select Field1, row_number() over (order by field1) rn

from Files t

where Field1>= @StartNo and Field1<= @EndNo

)

select x1.Field1+1 gap_start, x2.Field1-1 gap_end

from x x1 inner join x x2 on x1.rn=x2.rn-1

where x1.Field1!= x2.Field1-1

Where in the query must the cast (Field1 as bigint) be inserted?

I added our table name to the query.

DECLARE @StartNo BIgInt = 100;

DECLARE @EndNo BigInt = 200;

with

x as (

select Field1, row_number() over (order by field1) rn

from Files t

where Field1>= @StartNo and Field1<= @EndNo

)

select x1.Field1+1 gap_start, x2.Field1-1 gap_end

from x x1 inner join x x2 on x1.rn=x2.rn-1

where x1.Field1!= x2.Field1-1

Where in the query must the cast (Field1 as bigint) be inserted?

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

see my post ID: 40516055 ^^^

I posted the query, as is... just copy & paste...

changes are on line 6 & 8 in that script...

I posted the query, as is... just copy & paste...

changes are on line 6 & 8 in that script...

Hainkurt

Thank you, just ran the query but I still get the following error.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

Thank you, just ran the query but I still get the following error.

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

```
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select Cast(Field1 as bigint) Field1, row_number() over (order by Field1) rn
from Files t
where Cast(Field1 as bigint)>= @StartNo and Cast(Field1 as bigint)<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
```

then your Filed1 is not bigint!

if that's the case no solution exists...

run this to find those garbage entries

select isnumeric(Field1)=0 from Files

or, you can ignore (?) them maybe...

if that's the case no solution exists...

run this to find those garbage entries

select isnumeric(Field1)=0 from Files

or, you can ignore (?) them maybe...

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

here is the solution that ignores those invalid values:

```
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select Cast(Field1 as bigint) Field1, row_number() over (order by Field1) rn
from Files t
where isnumeric(Field1)=1 and Cast(Field1 as bigint)>= @StartNo and Cast(Field1 as bigint)<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
```

HainKurt

Thanks, the query returned the following data.

gap_start gap_end

158649 158653

158655 158664

158673 158681

158708 158719

158721 158724

158726 158885

158887 158887

How can I only display the missing numbers in the sequence?

Thanks, the query returned the following data.

gap_start gap_end

158649 158653

158655 158664

158673 158681

158708 158719

158721 158724

158726 158885

158887 158887

How can I only display the missing numbers in the sequence?

that requires a new question :)

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Open in new window

You can increase the number table by adding either changing the e3(n) line so that you cross join e2 with e2 or by adding another line to the number table.