I want to show only the initial date of delivery for specific orders. There could be many changes made to delivery dates but I want to show the very first one put in the system.
I tried with MIN but it gave me an error that it's not a timestamp data type so I tried with row number over partition by.... Here's the code:
SELECT
,'Initial Date' AS "ACTIVITY_NAME"
,TMP_RDD."EVENTTIME" AS "Requested Delivery Date"
,TMP_RDD."VBELN" AS "SO Number"
FROM( SELECT
"Requested Delivery Date",
"SO Number",
ROW_NUMBER () OVER (PARTITION BY "SO Number" ORDER BY "Requested Delivery Date" ) AS "RN"
FROM TMP_RDD ) AS TMP_RDD WHERE TMP_RDD."RN" = 1
Here's the sample table with changes to delivery dates:
| SO number (header) | Requested Delivery Date (value old) | Requested Delivery Date (value new) |
|---|---|---|
| 012 | 01.07.2022 | 03.07.2022 |
| 012 | 03.07.2022 | 05.07.2022 |
| 012 | 05.07.2022 | 20.06.2022 |
The problem is that it gives me the earliest date of all which isn't always correct as for example the initial delivery date could have been the 1st of July but the code gives me the the 20th of June.
Do you have any idea of what I could use here instead? In PQL, the formula that always works is PU_FIRST, what would be the equivalent of this in SQL (I'm also getting an error while trying to use FIRST_VALUE)