Find Missing entries

[bmacbmac]

Hello,

I have 4 columns with the data below

booktype, bookno, pageno, numofpages
DEED 100 1 5
DEED 100 6 4
DEED 100 10 1
DEED 100 11 2
DEED 100 14 1
DEED 101 1 2
DEED 101 3 4
DEED 101 7 5

Based on this data I know that book 100 page 1 is 5 pages long. So the next page has to be page 6. It is 4 pages long so the next page has to be 10....and so on.

Notice that page 11 is 2 pages long. The next page should be 13, but it's actually 14. This tells me that a page is missing at book 100 page 11.

I am looking for a way that I can identify instances like this. Maybe insert another column to flag if the math doesn't add up.

I could go two ways with the math:
page 1 + 5 pages = 6(the next page). If that is not true, flag the column.

OR page 6(2nd line) - 5 (numofpages from the previous line) - 1 (pageno from the previous line) should always = 0. If it does not, flag the column.

Problem is... I'm not sure how to include the two different rows in the math.

Also, the pages will start over on each book. So the page math will be off at the beginning of each book. I have about 1000 books so hoping I can tell it to do each book as it's own calculation.

I might just have to copy this all to Excel and do it there, but would be much faster if anyone had a SQL solution I could try.

Thanks in advance.

Brian

 

[markros]

It is very simple in SQL Server 2005 and up and also simple if you have ID column in this table in SQL Server 2000.

You just need to join with itself (self-join), e.g.

;with cte as (select *, row_number() over (order by BookType, BookNo order by PageNo) as RowNum from DeedBooks)

select c1.*, c2.PageNo from cte c1 LEFT JOIN cte C2 on c1.RowNum = c2.RowNum - 1 where C1.PageNo + C1.NumOfPages < c2.PageNo

 

[markros]

Make it an inner join or move condition into JOIN clause.

 

[bmacbmac]

Mi Markros. I always forget to mention that I am doing this in SQL 2000.

I tried the script above and imagine it was meant for 2005 as I get an error that row_number is not a recognized function name.

I created a a rowID identity column to try in SQL 2000 and came up with this:

select * from mytable T1
inner join mytable T2 on T1.RowID = T2.RowID -1
where t1.pageno + t1.numofpages < T2.pageno

So far it works perfectly! Thanks for the idea and please let me know if you see any glaring problems with my new code.

 

[markros]

It is going to work OK, I think.