CREATE TABLE [dbo].[test](

[custid] [int] NOT NULL,

PRIMARY KEY CLUSTERED

(

[custid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

//---------------------------------------

Insert the following values in the custid.

custid

-------

1

2

3

4

6

7

8

9

10

15

16

17

18

19

20

To Find missing no the SQL query should be:

select m.custid +1 as start

from test as m

left outer join test as r on m.custid = r.custid - 1

where r.custid is null

Output-

Start

---------

5

11

21

Description:first replicate the table as m and r,then its finding the null value from r(where r.custid is null) and joining with the previous value of null i.e r.custid-1 with the m table.

first it finds 4 as 5 is not in the r table and join 4 with the row of m where m.custid contains 4,similarly for 10 and 20.then it returns m.custid+1.

To find the range of missing values:

select start, stop from (

select m.custid + 1 as start,

(select min(custid) - 1 from test as x where x.custid > m.custid) as stop

from test as m

left outer join test as r on m.custid = r.custid - 1

where r.custid is null

) as x

where stop is not null;

Start Stop

------------

5 5

11 14

you can rewrite this query (without subqueries) as follows.

select l.custid + 1 as start, min(fr.custid) - 1 as stop

from test as l

left outer join test as r on l.custid = r.custid - 1

left outer join test as fr on l.custid <> m.custid) as stop

from test as m

left outer join test as r on ascii(m.custid) = ascii(r.custid) - 1

where r.custid is null

) as x

where stop <> '';

**How to find duplicate record in sql server:**

select id, count(*) from sequence

group by id

having count(*) > 1;

Reference:http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/

## No comments:

Post a Comment