CODEWITHSUNDEEP
sqlsql-servercursoridentity
Yasin Bilir
Yasin Bilir

Reputation: 304

How to check if the previous and next records are sequential in SQL

I have a table which has columns (ID int, RECEIPT_BARCODE nvarchar(200),FK_CLOSURE bigint) 

ID          RECEIPT_BARCODE          FK_CLOSURE  

1           01020011304500190001    58100000010019
2           01020011304500190002    58100000010019
1           01020011404500200001    58100000010020
2           01020011404500200002    58100000010020
3           01020011404500200003    58100000010020
1           01020011504500210001    58100000010021
1           01020011604500220001    58100000010022
1           01020011604500230001    58100000010023
2           01020011604500230002    58100000010023
1           01020011604500250001    58100000010025
3           01020011604500250003    58100000010025

Our program is automatically inserting the sales into the table. This table is being inserted like; when FK_CLOSURE up, ID is being set again to '1' (as you can see above) then it goes sequential as long as FK_CLOSURE doesnt change. When FK_CLOSURE change again, ID is set to '1'.

So the problem is; sometimes our sale program works wrong and doest insert a sale however increase the ID value as you can see in the last row. In the last row it increased ID from 1 to 3, there is no 2...

and i would like to report that kind of fails. I tried to use CURSOR but couldn't manage. Any suggestions ?

Upvotes: 1

Views: 1826

Answers (2)

ASh
ASh

Reputation: 35693

try this query 

declare  @SalesTable table (ID int, RECEIPT_BARCODE nvarchar(200),FK_CLOSURE bigint)

insert into @SalesTable values 
(1, '01020011304500190001',58100000010019),
(2, '01020011304500190002',58100000010019),
(1, '01020011404500200001',58100000010020),
(2, '01020011404500200002',58100000010020),
(3, '01020011404500200003',58100000010020),
(1, '01020011504500210001',58100000010021),
(1, '01020011604500220001',58100000010022),
(1, '01020011604500230001',58100000010023),
(2, '01020011604500230002',58100000010023),
(1, '01020011604500250001',58100000010025),
(3, '01020011604500250003',58100000010025)

select 
  FK_CLOSURE
from @SalesTable
group by FK_CLOSURE
having count(distinct ID) != MAX(ID)

when some IDs are missing for any FK_CLOSURE, then count(distinct ID) will be less than MAX(ID) for that FK_CLOSURE

output 

FK_CLOSURE
58100000010025

Upvotes: 2

James Z
James Z

Reputation: 12317

You can use logic where you compare the difference between row number and the key, and if the difference changes, then there's a hole in the id numbers. In your case the difference should probably be always zero. So something like this:

create table #test (ID int, RECEIPT_BARCODE nvarchar(200),FK_CLOSURE bigint) 

insert into #test values 
(1, '01020011304500190001',58100000010019),
(2, '01020011304500190002',58100000010019),
(1, '01020011404500200001',58100000010020),
(2, '01020011404500200002',58100000010020),
(3, '01020011404500200003',58100000010020),
(1, '01020011504500210001',58100000010021),
(1, '01020011604500220001',58100000010022),
(1, '01020011604500230001',58100000010023),
(2, '01020011604500230002',58100000010023),
(1, '01020011604500250001',58100000010025),
(3, '01020011604500250003',58100000010025)


select 
  ID, 
  RECEIPT_BARCODE, 
  FK_CLOSURE, 
  ID - row_number() over(partition by FK_CLOSURE order by ID asc)
from #test

And the result will be:

1   01020011304500190001    58100000010019  0
2   01020011304500190002    58100000010019  0
1   01020011404500200001    58100000010020  0
2   01020011404500200002    58100000010020  0
3   01020011404500200003    58100000010020  0
1   01020011504500210001    58100000010021  0
1   01020011604500220001    58100000010022  0
1   01020011604500230001    58100000010023  0
2   01020011604500230002    58100000010023  0
1   01020011604500250001    58100000010025  0
3   01020011604500250003    58100000010025  1

Upvotes: 3

Related Questions