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 /
COUNT(DISTINCT YEAR(DateField))
/, 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.