Sequence order

[Compkitty]

I'm looking to find gaps in sequences..

Ie.
DATA ID
1
2
3
4
6
7
8
9
A

I have 10 lines, w/ the number 5 missing, so I want to find which is missing then replace the A w/ the missing number. Else Delete the A...

Any Ideas??? THANKS ahead of time

 

[micha123]

Didn't test, but should be something like:

select ID+1 from Table t1
where not exists (select 1 from Table t2 
                 where t2.ID+1 = t1.ID)
AND ID < (select max(ID) from Table)

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Compkitty]

Can you break it down for me... I haven't ever used the WHERE NOT EXISTS **(yet)**
Ie...

select [block id]+1 from Table #TmpLergConvert
where not exists (select 1 from Table t2 
                 where t2.ID+1 = t1.ID)
AND ID < (select max(ID) from Table)

Where does the (1) come from in the select???

 

[SQLBill]

Did you look at the FAQs for this forum?

You should have found FAQ183-840 "How Can I Find Gaps in Date and Number Sequences?" by Terry Broadbent.

-SQLBill

Posting advice: FAQ481-4875

 

[micha123]

NOT EXISTS returns True if no rows returned from the select, and false if at least one row is returned.
The check stops after the first line is returned.
For that it is better than doing, for example, select count(*) from Tab and than checking if it is > 0.

You can write:
NOT EXISTS (SELECT * FROM...)
or:
NOT EXISTS (SELECT FLD1 FROM ...)
or a constant:
NOT EXISTS (SELECT 'micha' FROM...)

It doesn't matter since the number of rows returned from the subquery is the only thing that matters.

The best way is to use a constant, that is numeric, because like this the optimizer doesn't have to go to the dictionary to select or check column names.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Compkitty]

I did look at that article, however, I didn't think it worked for me (tried it, got errors) because some of my data has As.
PLEASE SEE DATA

Col02   Col02   Col03  Col04   Col05   Col06

566	210	478	0	6671	TX	
566	210	478	1	6671	TX	
566	210	478	2	6671	TX	
566	210	478	3	9533	TX	
566	210	478	4	9533	TX	
566	210	478	6	6671	TX	
566	210	478	7	6671	TX	
566	210	478	8	6671	TX	
566	210	478	9	6671	TX	
566	210	478	A	9533	TX

Using Cols 2 & 3 (as unique, as this is only partial data) there should be rows 0 - 9 (10 rows)
However, there is an A and Row 5 is missing... I need to be able to cycle thru the Cols 2 & 3 lookingfor gaps then replacing the gap w/ the right number replacing the A....

Does this help a little better??? I hope so... THANKS

 

[micha123]

SQLBill,
I'm sorry, generally I wouldn't "talk" like that - but in this case:

"I liked my solution better..."

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[micha123]

Compkitty,
Do all the records with characters contain 'A' instead of a sequential number, or are there more to it?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Compkitty]

No, There are more records and some don't have 'A' ... some just might have gaps... but either way they need to have rows 0-9 for every combo of col02 & col03 but if it's missing there is normally an 'a' in its place

 

[micha123]

Please check again which fields are making the uniqueness (I think you have a mistake there) - You ment either Col01+Col02+Col03+Col04 or Col02+Col03+Col04 or Col03+Col04?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Compkitty]

I'm sorry.. Ur Right.. it's col02+col03+col04

 

[micha123]

The following step will capture all the missing values into a temporary table:

select Col02 , Col03, Col04+1 as Col04
into #tmp
from #TmpLergConvert t1
where not exists (select 1 #TmpLergConvert t2 
                 where t2.Col02 = t1.Col02
                   AND t2.Col03 = t1.Col03
                   AND t2.Col04+1 = t1.Col04)
AND ID < 9 and id <> 'A'

The next step is to update the rows with the 'A':
(I suppose there can be only one 'A' for each Col02 and Col03!)

select Col02 , Col03, min(Col04) as min
into #tmp2
from #tmp
group by Col02 , Col03

update #TmpLergConvert
SET Col04 = t2.Col04
from #TmpLergConvert t1, #tmp2 t2
where t2.Col02 = t1.Col02
  AND t2.Col03 = t1.Col03
  AND t1.ID = 'A'

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Compkitty]

Ok, working w/ the first part..

select Col02 , Col03, Col04+1 as Col04
into #tmp
from #TmpLergConvert t1
where not exists (select 1 #TmpLergConvert t2 
                 where t2.Col02 = t1.Col02
                   AND t2.Col03 = t1.Col03
                   AND t2.Col04+1 = t1.Col04)
AND ID < 9 and id <> 'A'

Getting the following error

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'A' to a column of data type int.

 

[micha123]

Sorry... my fault... I kept the field name 'ID'...
I imagine that Col004 is a varchar or char, or else the value 'A' can not be there?
So, try the following:

select Col02 , Col03, Col04+1 as Col04
into #tmp
from #TmpLergConvert t1
where not exists (select 1 #TmpLergConvert t2 
                 where t2.Col02 = t1.Col02
                   AND t2.Col03 = t1.Col03
                   AND t2.Col04+1 = t1.Col04)
AND t1.Col04 < '9' and t1.Col04 <> 'A'

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[micha123]

And the same for next part:

select Col02 , Col03, min(Col04) as min
into #tmp2
from #tmp
group by Col02 , Col03

update #TmpLergConvert
SET Col04 = t2.Col04
from #TmpLergConvert t1, #tmp2 t2
where t2.Col02 = t1.Col02
  AND t2.Col03 = t1.Col03
  AND t1.Col04 = 'A'

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[SQLBill]

Micha123,

What are you talking about in this post?

SQLBill,
I'm sorry, generally I wouldn't "talk" like that - but in this case:

Quote:
"I liked my solution better..."

I don't understand what you mean by this: I'm sorry, generally I wouldn't "talk" like that - but in this case
and
I never said ANYWHERE "I liked my solution better...".

All I did was ask if the poster looked at the FAQs and then pointed out there was a FAQ on this subject. That then left it up to the poster to see if the FAQ solved their issue. At no time did I ever say it was a better solution than yours.

-SQLBill

Posting advice: FAQ481-4875

 

[micha123]

Hi, SQLBill,

I'm sory I've being misunderstood...

I simply wanted to say that, only once, without sounding rude or too proud, I liked my solution better.




"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[SQLBill]

Now I understand. That's okay...you should always show pride in your work.

You should add your solution to the FAQ list. Title something like:

How Can I Find Gaps in Date and Number Sequences-Another way.

That way both FAQs are listed together.

-SQLBill



Posting advice: FAQ481-4875

 

[micha123]

OK, SQLBill,
Thanks, I will do as you suggest.
M.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Compkitty]

Ok, I'm getting the same error again

SELECT NPA , NXX, BlockId+1 AS BlockID
INTO #tmp
FROM #TmpLergConvert t1
WHERE not exists (SELECT 1 FROM #TmpLergConvert t2 
                 WHERE t2.NPA = t1.NPA
                   AND t2.NXX = t1.NXX
                   AND t2.Blockid+1 = t1.Blockid)
AND t1.Blockid < '9' and t1.BlockId <> 'A'

ERROR

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'A' to a column of data type int.