Find missing numbers in sequential int values SQL Server -


suppose have table

bookno. | page 001     |  01 001     |  02 001     |  04 002     |  01 002     |  03 003     |  01 003     |  02 

is possible booknos missing page numbers?

this work, except page 1 missing (written on oracle syntax may different):

with cte1 ( select distinct    bookno   ,case when a.page + 1 != lead(page) on (partition bookno order page) page + 1 end missing_start   ,case when a.page + 1 != lead(page) on (partition bookno order page) lead(page) on (partition bookno order page) - 1 end missing_end test 1=1 ) select * cte1 missing_start not null order 1 ; 

result:

| bookno | missing_start | missing_end | |--------|---------------|-------------| | 001    | 3             | 3           | | 002    | 2             | 2           | 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -