Missing Sequence

Question

Missing Sequence

rmcknight

SSC Eights!

Points: 918
More actions
October 30, 2007 at 11:40 am

#62974

SQL2000
A table contains ID, CODE, and PRIORITY(varchar). For each ID Exists codes prioritized 1..n. Example :(ID, Code, PRI)
1 , A1, 01
1 , A1, 02
1 , F4, 03
1, D2, 04
ect.
How can I identify Codes that are missing or have gaps in the priority sequence. Example
2 , A3, 01
2 , A3, 02
2, D2, 04
2, D1, 05
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

I don't really see the missing sequence in your example. The ID doesn''t look unique and the priority apprears to be a char field. Here is a thread on finding missing sequences which may help you.

http://www.sqlservercentral.com/Forums/Topic398193-169-1.aspx

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

select t.id,t.code,t.pri

from table1 t left outer join

table1 u on t.id=u.id and t.pri=u.pri+1

where u.id is null and t.id>1

Should return all of the rows with a gap in priority BEFORE it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

Doesn't that identify IDs that have PRI gaps? And assumes that PRI is an int in a varchar field.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Isn't that what his example is showing? gaps in prio by id?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

I'm not sure what the example shows but the OP asked

How can I identify Codes that are missing or have gaps in the priority sequence.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Rough and crude but seems to answer the question:

SELECT Count(Code)AS 'Count of Code entries',Code, Max(Pri)AS 'Max Pri', Min(pri) AS 'Min Pri',

Cast(Max(Pri) AS Int) - CAST(Min(Pri)AS Int)+ 1 AS 'Diff'

FROM Table1

GROUP BY Code

HAVING CAST(Max(Pri) AS Int) > COUNT(CODE)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

rmcknight SSC Eights! Points: 918 More actions · Answer 7

Sorry for the confusion.

I'm looking for gaps in the sequence. The ID and Code are just record fields, and I need to know if someone skipped a sequence number. I know it's a weird case, I'm not sure how you repreresent something thats NOT there?

Randy

Sergiy SSC Guru Points: 110208 More actions · Answer 8

rmcknight (10/31/2007)
Sorry for the confusion.
I'm looking for gaps in the sequence. The ID and Code are just record fields, and I need to know if someone skipped a sequence number.

Then you need to tell your database what's your sequence.

Create a table with all numbers in your sequence.

After that you'll need just simple LEFT JOIN.

Nothing weird.

_____________
Code for TallyGenerator