How to search for a skip in sequence number?

Other SQL Server Topics
1

Hi

I'm looking for an sql script that can pick up where there is a skip in a sequence number. e.g, my table has a column for transaction number and it starts at number 1 every day (also has a date column). I'm for a script where I can search for skipped numbers by date. is that possible?

Thanks in advance

2

Starting the transaction number count each day seems odd.

When posting a question in a forum you should really provide some consumable test data with dates in ISO format:

CREATE TABLE #t
(
	TransDate date NOT NULL
	,TransNum int NOT NULL
	,PRIMARY KEY (TransDate, TransNum)
);
INSERT INTO #t
VALUES ('20210201', 1),('20210201', 2),('20210201', 4),('20210201', 5)
	,('20210202', 1),('20210202', 2),('20210202', 3)
	,('20210203', 1),('20210203', 3),('20210203', 5);

select * from #t;

Try something like the following:

WITH PrevNums
AS
(
	SELECT TransDate, TransNum
		,COALESCE(LAG(TransNum) OVER (PARTITION BY TransDate ORDER BY TransNum), 0) AS PrevNum
	FROM #t
)
SELECT P.TransDate, T.N AS MissingNum
FROM PrevNums P
	JOIN dbo.fnTally(1, 10000) T
		ON T.N > P.PrevNum
			AND T.N < P.TransNum
ORDER BY TransDate,  MissingNum;

The fnTally function is available from:

or use a number/tally table.

1 Like
3

Thank you for that, I will give it a try :slight_smile:

4

hi

i was trying to give a simple example on how to do your search for skip

My idea
Create Tally table 1... to .. 100
select From your table where NOT IN ( the numbers from Tally Table )

Turning into a nightmare ..
NOT IN is not recommended

Google Search .. gives an article which suggests 4 other methods ..
Not Exists , EXCEPT ..... blah blah
nothing worked

drop table #bb 
create table #bb
(
id int 
)

insert into #aa select 1
insert into #aa select 2
insert into #aa select 4

drop table #abc 
;WITH TallyTable AS (
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2 -- or you could use a large table from your ConfigMgr db if necessary
)
SELECT * into #abc FROM TallyTable;

select * from #bb where id not in ( select N from #abc )  

SELECT * FROM #bb A WHERE NOT EXISTS 
   (SELECT * FROM #abc B WHERE B.N = A.id )

image
image503×630 11.7 KB

can any one help me understand .. what the $%^#@&^* is going on