Quote:I want two other conditions to be met.
1. I want those in arrears for a certain numbers of years (e.g. 5) be retrieved and
2. I want to search for those in arrears for a certain amount (e.g. 500)
So, to get specific values via sql window functions:
1) distinct count of years /
/, use DENSE_RANK
]. For example:
DENSE_RANK() OVER (PARTITION BY YEAR(DateField) ORDER BY [AreaNameOrKey])
2) sum of amount
SUM([Amount]) OVER (PARTITION BY [AreaNameOrKey] ORDER BY [AreaNameOrKey])
Note that i have no idea about your data structure. So this is a generic tip.