Find consecutive numbers 3 or more within a set

[wbodger]

I am trying to find instances where there are 3 or more consecutive check numbers for a given account. For instance, if I have:

Account CheckNumber Amount
001234  34546       500
001234  34547       1100
001234  34890       6
001234  35000       1000
001234  35001       1500
001234  35002       1000
001234  35003       1400
0056789  51849      456
0056789  51850      1800
0056789  52860      1234
0056789  53500      500
0056789  53501      500
0056789  53502      500

the query should return:
001234 35000
001234 35001
001234 35002
001234 35003

and then

0056789 53500
0056789 53501
0056789 53502

I can write it with Lead and specify a number of records (I thought the requirement was for 3) and it works great, but the requirement is for 3 or more, so I do not know how many there might be, but it must be at least 3. Additionally, I want to filter if they add up to less than #3k, but where I am stuck (I think) is the initial query being flexible.

Thank you for any help you can give.

wb

 

[gmmastros]

This type of problem is "famous" in the SQL Server universe. It's known as the "gaps and islands" problem. I encourage you to google it, "SQL Server gaps and islands"

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Had a whole reply typed up, decided to try one more thing and... I think that I've got it. Thanks for pointing me to the Islands!

 

[gmmastros]

You're welcome. I wish I could have helped more, but I suspect it wouldn't have been as good as what you found on Google.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom