CODEWITHSUNDEEP
sqlsql-server
codingNightmares
codingNightmares

Reputation: 313

How to filter out results based on a date in SQL

I have a query result with two columns, The second column is the date in epoch unix time. The query takes a date parameter for example '7/1/2017'

What I want to do is choose the leaseCode that is most recent to the date entered. So in the sample below the first result EDMGW | 1533013200000 would be closer to "7/1/2017" than the second result.

I do not want the second EDMGW | 1690779600000 row in the result.
Any ideas you SQL gurus?

LseCode      | ExpDate  
-------------+---------------  
EDMGW        | 1533013200000  
EDMGW        | 1690779600000  
CHA08LL      | 1496210400000  
FRE05LL      | 1559282400000

Upvotes: 0

Views: 205

Answers (4)

Xingzhou Liu
Xingzhou Liu

Reputation: 1559

DEFINE @param DATE = '07/01/2017';

SELECT TOP 1 leasecode 
FROM t WHERE convert(date,dbo.ToDateTime(ExpDate)) <= convert(date,@param)
ORDER BY t.date DESC;

by most recent do you mean the first record with a date on or before the user provided date, or closest as in absolute difference to the date? Also, since you're using DAY, how do you want to deal lease codes on the same day - ties? Sry Can't test the date conditions, not before a machine at the moment.

/* one row only - with arbitrary resolution of ties absolute dstance */
WITH d AS (
    SELECT *,ROW_NUMBER() OVER (ORDER BY 
    ABS( datediff(day, CONVERT( date, dbo.ToDateTime(ExpDate) ) , convert( date, @param ) ) AS dst FROM t
)
SELECT * FROM d WHERE dst = 1;

/* all rows that with and ExpDate the same number of absolute days from your parameter */
WITH d AS (
    SELECT *,DENSE_RANK() OVER (ORDER BY 
    ABS( datediff(day, CONVERT( date, dbo.ToDateTime(ExpDate) ) , convert( @param , date ) ) ) AS dst FROM t
)
SELECT * FROM d WHERE dst = 1;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Gordon's answer may be sufficient for your needs, and if so then go with it. If you really want a query which will find the entry closest to a given date for each lease code, then we must do more work. One approach is to convert the expiration date from milliseconds since the epoch into a SQL Server date, and then calculate the absolute difference in seconds between another date parameter which you choose (e.g. 2017-07-01).

SELECT t.*
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY LseCode
            ORDER BY ABS(DATEDIFF(S, DATEADD(S, [ExpDate]/1000, '1970-01-01'), '20170701'))) rn
    FROM yourTable
) t
WHERE t.rn = 1

Demo here:

Rextester

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

If you meant that the closest date should be on or after 2017-07-01, you can use top along with some dateadd logic to convert the epoch to date for getting the date difference and using it to order the results.

select top 1 with ties lseCode,expDate
from t
where datediff(day,'2017-07-01',dateadd(second, expDate/1000, '1970-01-01')) >= 0
order by datediff(day,'2017-07-01',dateadd(second, expDate/1000, '1970-01-01'))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by lsecode order by expdate asc) as seqnum
      from t
     ) t
where seqnum = 1;

For your case, I am guessing that you really mean the earliest ExpDate.

Upvotes: 0

Related Questions