Date Diff from the max date partition by value SQL Teradata

Question

please I have the below data that I need to calculate the date diff between the Currunt_date and the Max Date for each ID and the result of the date diff will be located once beside the max date and the other records return NULL. I ask if it could be handled with a window function if available without joining with the max value because the real query is very complicated and get a very huge volume of data with millions of rows and I need to optimize it to enhance the query performance

|ID  |Date  |
|----+------|
|A   |1-Apr |
|A   |15-Apr|
|B   |1-Mar |
|B   |15-Mar|
|C   |1-Jan |
|C   |15-Jan|

I tried to use the below query but it duplicates the result with each date

SELECT ID, DATE, Current_date - Max(Date) over (Partition BY ID ORDER BY DATE DESC) AS DURATION
FROM TBL

But I need the result to be like the below

|ID  |Date  |Duration|
|----+------+--------|
|A   |1-Apr |NULL    |
|A   |15-Apr|17      |
|B   |1-Mar |NULL    |
|B   |15-Mar|48      |
|C   |1-Jan |NULL    |
|C   |15-Jan|107     |

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2021-05-02T04:11:43

I would use MAX here as an analytic function to identify the records having the max date per ID:

SELECT
    ID,
    Date,
    CASE WHEN Date = MAX(Date) OVER (PARTITION BY ID)
         THEN CURRENT_DATE - MAX(Date) OVER (PARTITION BY ID) END AS Duration
FROM cte
ORDER BY ID, Date;

Date Diff from the max date partition by value SQL Teradata

1 Answers