Click here to Skip to main content
14,880,638 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Members,

Please I'm stuck in an SQL query and I need your help.

I have a list of people who are in arears for rent. Now, I have a query that distinctively retrieves those in arrears as shown below. Apart from the query being distinctive, 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 the code below only distinctively retrieves those who are in arrears less than the current year and I want those in arrears for a certain number of years e.g. 5 years and those in arears for a certain amount when entered.

Thanks.

What I have tried:

SQL
Select *
from
(
  select *, row_number() over (partition by FileNo order by EndDate Desc) as row_number
  from RentPayment
) as rows 
where row_number=1 And StartYear < @CurrentYear
Posted
Updated 16-Mar-21 0:17am
v2
Comments
Richard Deeming 15-Mar-21 8:36am
   
Show us the structure of your table, some anonymised sample data, and an explanation of how you would decide whether or not to include each sample row in your output.

1 solution

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:
SQL
DENSE_RANK() OVER (PARTITION BY YEAR(DateField) ORDER BY [AreaNameOrKey])


2) sum of amount
SQL
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.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900