SQL Query to distinctively retrieve rows based on some number of years and amount

Question

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.

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

Answer 1

Hi @Benniit ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Suppose there is another column named Amount.

Please refer below and check whether it is helpful:

declare @Year int  
declare @amount int  
set @Year=YEAR(GETDATE())-5  
set @amount=500  
  
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 < @Year and Amount=@amount  
  
--or avoid using row_number()  
Select *    
from RentPayment  
where StartYear < @Year and Amount=@amount      

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.