CODEWITHSUNDEEP
sqloracle-databasedateoracle11g
Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

Get date nearest to sysdate

I would like to get per N the max(date) <= sysdate. If no dates in the past/today exist I want the min(date). So I do not want the absolute date nearest to sysdate.

Testdata

CREATE TABLE DATTEST (N NUMBER, D DATE);

INSERT INTO DATTEST (N,D) VALUES (1,TRUNC(SYSDATE-2000));
INSERT INTO DATTEST (N,D) VALUES (1,TRUNC(SYSDATE-1000));
INSERT INTO DATTEST (N,D) VALUES (1,TRUNC(SYSDATE+100));

INSERT INTO DATTEST (N,D)VALUES (2,TRUNC(SYSDATE));
INSERT INTO DATTEST (N,D)VALUES (2,TRUNC(SYSDATE+1000));


INSERT INTO DATTEST (N,D)VALUES (3,TRUNC(SYSDATE+1000));

So far I've got this. It gives the right results bus does two tablescans. I'm working on large tables and this query is called many times. I've been breaking my head converting this to a single table scan. 

with nums as 
    (SELECT LEVEL num
     FROM DUAL
     CONNECT BY LEVEL <= 3
    ) 
select 
num
,(nvl((select  max(d)
    from dattest
    where d <= trunc(sysdate)
    and n = num),
    (select min(d)
    from dattest
    where d > trunc(sysdate)
    and n = num))
)
from nums;

Expected output

1   26-06-12
2   23-03-15
3   17-12-17

Upvotes: 1

Views: 1014

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

How about using an aggregation, with some conditional logic?

select id,
       coalesce(max(case when d <= trunc(sysdate) then d end),
                min(d)
               )
from table t
group by id;

Upvotes: 2

Related Questions