CODEWITHSUNDEEP
sqloracle-databasejoinplsql
Jimenemex
Jimenemex

Reputation: 3166

Selecting most recent result in Oracle SQL

I'm trying to select the most recent row (Interest Rate IntRate) based on two dates. The most priority date is EffDate while the second is LastMaintenance. EffDate has the date the IntRate was put into effect. So the format has something like this, MM/DD/YYYY. The LastMaintenance column has the last time the row was altered in the format of SYSDATE.

For example, if I change the IntRate within the same day, I would get two rows in the history table. Both of which will have the same EffDate, but different LastMaintenance dates.

Right now, I'm trying to select the most recent IntRate based off a table which has a history of IntRate for a given account. So I want to select the most recent IntRate based upon a date. The closest IntRate to that date is what I want to use.

The problem is, I can't figure out how to select the most recent IntRate when the EffDate is the same, but the DateLastMaintenance is different.

In the picture below, How do I select the IntRate where the value is null? I want that one because even though the EffDate is the same, the DateLastMaintenance field is newer for the null value.

Example

I have something like this to get the most recent EffDate for a given AcctNbr and the date where I want the most recent value of.

SELECT EffDate
    FROM (SELECT *
    FROM AcctRateHist X
    WHERE X.AcctNbr = A.AcctNbr -- R.Acctnbr
    AND X.EffDate <= TO_DATE('09-26-2017', 'MM-DD-YYYY') -- The date to get the most recent value for
    ORDER BY X.EffDate DESC, X.DateLastMaint DESC)
WHERE ROWNUM = 1;

This will get me an EffDate of 9/14/2017, but when I hook this up to an outer query I get all the rows with an EffDate of 9/14/2017, so I need to look at the DateLastMaintenance column now.

I tried to select the ROWNUM = 1, but it get's the IntRate of 0.0375 and not the null value one (Where the ROWNUM = 2, which I don't get why it picks up that one..).

SELECT R.AcctNbr, X.EffDate, X.IntRate, X.InactiveDate, X.DateLastMaint
FROM AcctRateHist X, Rpt_Sort_Process R
WHERE X.AcctNbr = R.AcctNbr -- Just some stuff thats needed to get the AcctNbr: Ignore
AND R.QueNbr = somenum -- Just some stuff thats needed to get the AcctNbr: Ignore
AND R.ApplNbr = somenum -- Just some stuff thats needed to get the AcctNbr: Ignore
AND R.QueSubNbr = somenum -- Just some stuff thats needed to get the AcctNbr: Ignore
AND X.EffDate = (SELECT EffDate -- This gets the EffDate of 9/14/2017
                FROM (SELECT *
                        FROM AcctRateHist X
                        WHERE X.AcctNbr = A.AcctNbr -- R.Acctnbr
                        AND X.EffDate <= TO_DATE('09-26-2017', 'MM-DD-YYYY') -- The date to get the most recent value for
                        ORDER BY X.EffDate DESC, X.DateLastMaint DESC)
                WHERE ROWNUM = 1)
AND ROWNUM = 1 -- This SHOULD return the IntRate of null, but doesn't
ORDER BY X.EffDate DESC, X.Datelastmaint DESC;

Upvotes: 1

Views: 270

Answers (2)

Michael O&#39;Neill
Michael O&#39;Neill

Reputation: 954

Windowing functions are ideal to this task. Here's a DENSE_RANK example.

create table acctratehist ( acctnbr int, intrate number (9,3), effdate date, datelastmaint timestamp);

insert into acctratehist values (89, 2.125, DATE '2018-03-11', TIMESTAMP '2018-03-11 11:00:00');
insert into acctratehist values (89, 2.375, DATE '2018-03-11', TIMESTAMP '2018-03-11 15:00:00');
insert into acctratehist values (89, 2.825, DATE '2018-03-12', TIMESTAMP '2018-03-12 13:00:00');
insert into acctratehist values (52, 2.000, DATE '2018-03-11', TIMESTAMP '2018-03-11 15:00:00');
insert into acctratehist values (52, 2.333, DATE '2018-03-12', TIMESTAMP '2018-03-12 13:00:00');

select t.acctnbr, t.effdate, t.intrate
from (
  select acctnbr, intrate, effdate, dense_rank() over (partition by acctnbr, effdate order by datelastmaint desc) as dlmrank
  from acctratehist
) t
where t.dlmrank = 1
order by t.acctnbr, t.effdate

SQLFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you want to use tuples:

(X.EffDate, X.DateLastMaint) =
    (SELECT EffDate, DateLastMaint -- This gets the EffDate of 9/14/2017
     FROM (SELECT *
           FROM AcctRateHist X
           WHERE X.AcctNbr = A.AcctNbr AND
                 X.EffDate <= DATE '2017-09-26' -- The date to get the most recent value for
           ORDER BY X.EffDate DESC, X.DateLastMaint DESC)
     WHERE ROWNUM = 1
    )

Upvotes: 1

Related Questions