Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CactusJuice
Starting Member
46 Posts |
 Posted - 2002-11-01 : 12:33:27
|
| This one is stumping me and I'm wondering if there is a low-overhead solution. How can check for the first break in a sequence? Say records look like this: always 6 numbers plus one trailing letter between A and F. For example, with entries such as "102544A", "102544B", "102544D", "102544E". In this case I need to know how to check if there is a break in sequence. "102544C" is missing. Another example is "124332A", "124332B", "124332C". I need to know that there is no break in sequence and that "124332D" is comes next. I think I figured this one out, I can do a RIGHT(string,1) converting to ASCI and sorting by the numeric ASCI and then incrementing by 1. I guess I'm mostly stuck on how to find the next item in sequence if the break occurrs in the middle. Any ideas on how I might query this? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-01 : 12:39:30
|
| Take a look here:http://www.sqlteam.com/item.asp?ItemID=3332Also read the original thread linked inside that one.It's a method for filling in the blanks in a discountinuous date range, you can easily use it to fill in the blanks for the missing values. By LEFT JOINing the interpolated table to the actual table, you can test the actual values for a NULL condition; that would indicate a gap. Something like:SELECT A.* FROM (interpolatedTable) A LEFT JOIN ActualTable BON A.Entry=B.EntryWHERE B.Entry IS NullYou'd need to substitute the SELECT statement that generates the values for the interpolatedTable portion of the FROM clause. Also try searching the forums for "tally", you'll find some other methods for generating data ranges.Edited by - robvolk on 11/01/2002 12:41:04 |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2002-11-03 : 22:06:51
|
| >> ... How can check for the first break in a sequence? Say records look like this: always 6 numbers plus one trailing letter between A and F. <<CREATE TABLE A_thru_F(letter CHAR(1) NOT NULL PRIMARY KEY);INSERT INTO A_thru_F VALUES (..);CREATE TABLE Foobar(seq CHAR(7) NOT NULL CHECK seq LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][A-F]', .. ); SELECT SUBSTRING (F1.seq, 1, 6), MIN(A1.letter) FROM A_thru_F AS A1, MIN(A1.letter) FROM A_thru_F AS A1, Foobar AS F1 WHERE A1.letter NOT IN (SELECT SUBSTRING (F1.seq, 7, 7) FROM Foobar AS F2 WHERE F2.SUBSTRING (F1.seq, 1, 6) = F1.SUBSTRING (F1.seq, 1, 6));--CELKO--Joe Celko, SQL Guru |
|
|
|
SamD
Starting Member
1 Post |
Posted - 2011-04-27 : 11:53:01
|
| Hi I have a similar problem .. I am essentially trying to look through a column of order numbers ( by person)and trying to see if there is a break in the sequence.. also i am allowed to have a break of X items in this list, possibly more than once. The catch is the sequence of order numbers changes by person and the X factor has to be specified ( like i can have different break allowances). Any help is appreciatedThanks!Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-27 : 11:55:22
|
| You'll need to post sample data, expected results, and any queries you've attempted so far.For future reference, it's better to start a new thread rather than post on one that's had no activity for several months (much less 8.5 years). |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-27 : 12:14:43
|
| something like this?;with cte as(select name, num, seq = ROW_NUMBER() over (partition by name order by num)from tbl)select * from(select cte1.name, cte1.num, gap=cte1.num-cte2.numfrom cte c1join cte c2on cte1.name = cte2.nameand cte1.seq = cte2.seq+1 )where gap>=@ioops v2000 forum - are you v2000?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
How to check for a Break in a sequence?