CODEWITHSUNDEEP
sqloracle-databaseplsql
sitara
sitara

Reputation: 3

Finding Max of Previous records value based on condition in Oracle

I need to find the max of Previous Transaction End Date where Previous record's Start and End Dates are less than or equal to the current Start Date records.

The first Record (ID: 101) will be null as this is the first transaction. I have tried using Lag function, but struck on how to compare the current record Start Date with all the Previous Start and End Dates.

Transaction ID Transaction Start Date Transaction End Date Last Transaction Date
-------------- ---------------------- -------------------- ---------------------
           101 07/07/2015             07/09/2015           null
           102 10/06/2015 10/08/2015  07/09/2015
           103 10/08/2015 10/08/2015  10/08/2015
           104 10/08/2015 10/08/2015  10/08/2015
           105 10/27/2015 10/28/2015  10/08/2015
           106 10/27/2015 10/28/2015  10/08/2015
           107 10/27/2015 10/29/2015  10/08/2015
           108 10/27/2015 10/30/2015  10/08/2015
           109 10/29/2015 10/29/2015  10/29/2015
           110 11/10/2015 11/12/2015  10/30/2015

Upvotes: 0

Views: 240

Answers (3)

Amith Kumar
Amith Kumar

Reputation: 4874

If I understand your requirement correctly, you can achieve this by using select subquery. Let me know if you need additional changes, and I can compile that for you.

SELECT t.id,
       t.startDt,
       t.endDt,
  (SELECT max(t1.endDt)
   FROM transactions t1
   WHERE t1.endDt <= t.startDt
     AND t1.id < t.id) AS lastTxDt
FROM transactions t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

One method is a correlated subquery:

select t.*,
       (select max(t2.enddate)
        from t t2
        where t2.id < t.id and
              t2.enddate <= t.startdate
       )
from t;

This assumes that the start date is on or before the end date, so a condition on start date is redundant.

This also assumes that "previous records" is based on the id. You can remove that condition, if you just mean any records that occur before chronologically.

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

Since you edited your data set for like 5 times or more I had to test it over and over. Please consider other's people time and effort devoted to help you . . .

Anyways, here's the solution considering table name is t and columns are tid, ts, te which are short for transaction id, start and end dates:

select 
  t.tid as "Transaction ID", 
  t.ts as "Transaction Start Date",
  t.te as "Transaction End Date",
  max(t2.te) as "Last Transaction Date"
from t
left join t t2 on
  t.ts >= t2.ts -- might not be needed, if end date > start date always (should be)
  and t.ts >= t2.te
  and t.tid > t2.tid
group by t.tid, t.ts, t.te
order by t.tid

Result:

 Transaction ID | Transaction Start Date | Transaction End Date | Last Transaction Date
----------------+------------------------+----------------------+-----------------------
            101 | 2015-07-07             | 2015-07-09           | NULL
            102 | 2015-10-06             | 2015-10-08           | 2015-07-09
            103 | 2015-10-08             | 2015-10-08           | 2015-10-08
            104 | 2015-10-08             | 2015-10-08           | 2015-10-08
            105 | 2015-10-27             | 2015-10-28           | 2015-10-08
            106 | 2015-10-27             | 2015-10-28           | 2015-10-08
            107 | 2015-10-27             | 2015-10-29           | 2015-10-08
            108 | 2015-10-27             | 2015-10-30           | 2015-10-08
            109 | 2015-10-29             | 2015-10-29           | 2015-10-29
            110 | 2015-11-10             | 2015-11-12           | 2015-10-30

You couldn't use lag() analytic function because it returns value for only one row (previous, if not specified otherwise). This is where you need JOIN with your comparison on dates and ensuring that you only take data from previous transactions t.tid > t2.tid. Then take the max(t2.te) for those records who were a match.

Upvotes: 0

Related Questions