Solved

T-SQL showing missing document numbers

Posted on 2014-12-22
25
40 Views
Last Modified: 2015-04-02
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?
0
Comment
Question by:Yeaktom
  • 10
  • 9
  • 4
  • +2
25 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40513073
If the sequence is numeric, you can use a number table left joined to your table on the sequence field and eliminate all records where your sequence field is null. In SQL 2K5 and above, this would be the general idea:
;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 <your table> ON e4.n = <your table>.Field1
WHERE Field1 IS NULL
ORDER BY n;

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.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40513086
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40513544
;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;
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:Yeaktom
ID: 40514397
@ 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?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40514826
CAST(Field1 AS BIGINT)
0
 
LVL 32

Expert Comment

by:awking00
ID: 40514831
;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;
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40514854
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'.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40515110
Sorry, left out a parenthesis -
min(cast(field1 as bigint)) ==> line 2
max(cast(field1 as bigint)) ==> line 5
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40515403
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

Open in new window

0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516018
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'.
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516019
@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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516049
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

Open in new window


above is with a sample data... working fine...
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516051
Hi HainKurt

Field1 is a varchar(255)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516052
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

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

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516055
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

Open in new window

0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516057
@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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516060
i don't see your changes!
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516061
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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516062
see my post ID: 40516055 ^^^

I posted the query, as is... just copy & paste...
changes are on line 6 & 8 in that script...
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516064
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.


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

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516066
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...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516067
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

Open in new window

0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40516074
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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516596
that requires a new question :)
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40516621
we need a view for all numbers first...

i use this

create view n as (
select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
);

Open in new window


then, we can use this to get all numbers 1..999999

 create view nx6 as (select n6.n*100000 + n5.n*10000 + n4.n*1000 + n3.n*100 + n2.n*10 + n1.n as n from n n1, n n2, n n3, n n4, n n5, n n6)

Open in new window


now, we need to join both

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 nx6.n, x1.Field1+1 gap_start, x2.Field1-1 gap_end
  from x x1 inner join x x2 on x1.rn=x2.rn-1 inner join (select n from nx6 where n>=@StartNo and n<=@EndNo) nx6 on nx6.n > x1.Field1 and nx6.n < x2.Field1
 where x1.Field1!= x2.Field1-1
 order by nx6.n

n	gap_start	gap_end
123	123	124
124	123	124
126	126	131
127	126	131
128	126	131
129	126	131
130	126	131
131	126	131

Open in new window


just comment out/remove line 5 above in your case...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database ERD 4 28
sql, case when & top 1 14 27
AD and SQL Server 2016 2 28
What is the best way to use power bi and ssrs 3 25
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question