Lukas Eder discusses window functions:
Now, let’s assume I’m interested in these things:
- How many payments were there in the same hour as any given payment?
- How many payments were there in the same hour before any given payment?
- How many payments were there within one hour before any given payment?
Those are three entirely different questions.
Lukas’s solution uses Oracle syntax, but most of it also applies to SQL Server 2012 and higher. The part that doesn’t apply, unfortunately, is the RANGE BETWEEN INTERVAL, which allows you to find values clustered in the same time period (one hour in his example).