0

Dear Experts

Sqlserver Table1holds data as follows

date---------------sno
01/01/2010-------1
01/01/2010-------2
01/01/2010-------4
01/01/2010-------5
01/01/2010-------7
02/01/2010-------1
02/01/2010-------2
02/01/2010-------6
02/01/2010-------8

How to findout missed sno in 01/01/2010?

I want to get this result
3
6

Please help

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by padtes
0

I can think of a sotred proc, where you will generate a temp table of 1 column and insert 1 .. max(sno) and then delete existing.

create table #tmpNum (id int)
declare @mx int
select @mx=max(sno) from table1
while @mx >= 1
begin
  insert into #tmpNum values (@mx)
  set @mx = @mx -1
end

delete from #tmpNum where #tmpNum.id in (select sno from table1)
select id from #tmpNum order by id
drop table #tmpNum
Go

I can think of an Excel based solution, if this is one time data fixing. Get all records into excel, sort by sno. Add column in Excel with formula something like (assuming sno is in column b and data starts with row 1)
For added column C: cell c2 formula will be =(b2=b1-1) Then copy the fomula for all of C column, this will fill the column C with TRUE / FALSE.
Then filter on FALSE. These are jumped sNo.
Let us know your solution if any.
Feel free to close thread if this is sufficient.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.