Sql to get data whose expiry date is coming close-CodePudding

I have a table called "Listings" as below :

Id      RealEstateAgentId       DepartmentId    Status      CityId      ProvinceId      CountryId       
ListingPostedDate           ListingExpirydate

Status column can have these values:

1 - Active
2 - Sold
3 - Expired

Now, I want to get top 10 active listings having the closest expiry date by sorting by number of days left, from lowest to highest. If the expiry date of more than 1 listings are the same, then we will sort according to the Id primary column (from lowest to highest).

But I am not getting the logic for the closest expiry date.

For example: if it would have been to find the listings whose expiry date is in 2 months then something like below should be fine :

select * 
from Listings 
where ListingExpirydate < dateadd(month, 2, getdate());

Can someone help me with idea or logic?

CodePudding user response：

Now, I want to get top 10 Active Listings

SELECT TOP(10)

WHERE status = 1

having the closest Expiry Date by sorting by number of days left, from lowest to highest. If the Expiry Date of more than 1 listings are the same, then we will sort according to the "Id" primary column (from lowest to highest).

ORDER BY listingexpirydate, id

The complete query:

select top(10) *
from listings 
where status = 1
order by listingexpirydate, id;

CodePudding user response：

closest Expiry Date

for this, we should check the current date with ListingExpirydate column to get closet expiring records

DATEDIFF(day, l.ListingExpirydate, GETDATE())

for Top 10 and active record applied these filters to query:

select top(10) l.*
...
where status= 1

and final code is below:

 select top(10) l.*, 
        DATEDIFF(day, l.ListingExpirydate, GETDATE()) daysLeftToExpire 
 from Listings l
 where status= 1 
 order by DATEDIFF(day, l.ListingExpirydate, GETDATE()) , id