sqlteradata

Teradata SQL using recursive logic in SQL

I am using Teradata and there is a scenario which I am not sure how to achieve it using sql query. I have a table with data as given below. The table is grouped based on the columns GP,Key1,Key2 and Key3. Now my requirement is that, I need to populate the start date from the column process date, for few of the ID.

enter image description here

GP ID start date process date Key1 key2 key3
W1 W003 Current_Date 2023-04-24 1asd 345 456
W1 W004 2024-08-20 1asd 345 456
W1 W005 2024-08-21 1asd 345 456
W1 W0010 2024-08-22 1asd 345 456
W1 W0120 2024-08-23 1asd 345 456
W1 W0015 2024-08-24 1asd 345 456
W1 W0016 2024-08-25 1asd 345 456
W2 W234 Current_Date 2021-05-27 2def 111 222
W2 W345 2022-03-21 2def 111 222
W2 W456 2023-03-21 2def 111 222

Now the case here is, in the column ID for the value W004 and W005, I need to populate start date as process date from W003.

Similar way in the column ID for the value W0010,W0120,W0015 and W0016 , I need to populate start date as process date from W005

When it comes to the ID W345 and W456, the start date needs to populated with W234 process start date.

Result table should look as below

enter image description here

I know we can do join with the same table multiple time and try to achieve this, but just thinking is there anyway we can achieve it using a single query. Please note that the connection to IDs which need to populated for start date and from ID where we take the value wont change. For ex. W004 and W005 always take the value from W003 , like that W0010,W0120,W0015 and W0016 always take value from W005

Solution

  • Sounds like a variation of conditional aggregation using Windowed Aggregates:

    case
  when id in ('W004', 'W005') 
    then max(case when id = 'W003' then process_date end) over (partition by GP)
  when id in ('W0010', 'W0120', 'W0015', 'W0016') 
    then max(case when id = 'W005' then process_date end) over (partition by GP)
  else current_date
end