question

Benniit-3632 avatar image
0 Votes"
Benniit-3632 asked MelissaMa-msft commented

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

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

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Benniit

Thank you for contacting Microsoft Open Specifications Support. It appeared your question is not related to Protocols issues mentioned here https://docs.microsoft.com/en-us/openspecs/main/ms-openspeclp/3589baea-5b22-48f2-9d43-f5bea4960ddb. Your post is now retagged sql-server-general.

Hung-Chun Yu
Microsoft Open Specifications Support.

0 Votes 0 ·

For this type of question, we generally recommend that you post CREATE TABLE statements for your tables together with INSERT statements for some sample data, enough to illustrate all angles of the problem. And then we need to know the the expected results from that data.

With the information, we will have to guess what your table looks like, and most likely we will guess wrong and it will be frustrating for everyone.

On the other hand, a script with tables and data helps to clarify what you are looking for, and we can copy and paste into a query window to develop a tested solution.

0 Votes 0 ·

Hi @Benniit-3632,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @Benniit-3632,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.