Find Max of Previous records Value based on condition

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Fan

Find Max of Previous records Value based on condition

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 of each status ID and Status line combination will be null. 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 Status ID Status Line Transaction Start Date Transaction End Date Last Transaction Date
ABC~101 1000 010 7/7/2015 7/9/2015 null
ABC~102X 1000 010 10/6/2015 10/8/2015 7/9/2015
ABC~103 1000 010 10/8/2015 10/8/2015 10/8/2015
ABC~104 1000 010 10/27/2015 10/28/2015 10/8/2015
ABC~105 1000 010 10/27/2015 10/28/2015 10/8/2015
ABC~106 1000 010 10/27/2015 10/29/2015 10/8/2015
ABC~107 1000 010 10/27/2015 10/30/2015 10/8/2015
ABC~108 1001 010 10/29/2015 10/29/2015 null
ABC~109 1001 010 11/10/2015 11/12/2015 10/29/2015
Tags (2)

Accepted Solutions
Junior Contributor

Re: Find Max of Previous records Value based on condition

I doubt you can get that result with a single step, this should return the expected result:

SELECT dt.*,
   --  use max previous end date only if <= current start date
   Last_Value(CASE WHEN max_end_date <= Transaction_Start_Date THEN max_end_date end IGNORE NULLS) 
   Over (PARTITION BY Status_ID
         ORDER BY Transaction_Start_Date, Transaction_End_Date 
         ROWS Unbounded Preceding)
FROM
 (
   SELECT tab.*,
      Max(Transaction_End_Date) -- max previous end date
      Over (PARTITION BY Status_ID
            ORDER BY Transaction_Start_Date, Transaction_End_Date
            ROWS BETWEEN Unbounded Preceding AND 1 Preceding) AS max_end_date
   FROM tab
 ) AS dt

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Find Max of Previous records Value based on condition

I doubt you can get that result with a single step, this should return the expected result:

SELECT dt.*,
   --  use max previous end date only if <= current start date
   Last_Value(CASE WHEN max_end_date <= Transaction_Start_Date THEN max_end_date end IGNORE NULLS) 
   Over (PARTITION BY Status_ID
         ORDER BY Transaction_Start_Date, Transaction_End_Date 
         ROWS Unbounded Preceding)
FROM
 (
   SELECT tab.*,
      Max(Transaction_End_Date) -- max previous end date
      Over (PARTITION BY Status_ID
            ORDER BY Transaction_Start_Date, Transaction_End_Date
            ROWS BETWEEN Unbounded Preceding AND 1 Preceding) AS max_end_date
   FROM tab
 ) AS dt

 

Fan

Re: Find Max of Previous records Value based on condition

Hi