Search code examples
informatica-powercenterteradata-sql-assistant

Sort data by date & time and then select the next min date to update the other date column in informatica

Have Data like this This is a teradata table which is my source in informatica

 S_ID      A_ID     START_DATE          END_DATE      STATUS

   1          a         01-02-2017         01-03-2017     CLOSED

   1          a         01-03-2017         31-12-9999     CLOSED       

   1          b         01-04-2017         31-12-9999     CLOSED

   1          c         01-05-2017         31-12-9999     ACTIVE

Now my requirement is this set of data below: how can i use or modify my source table to get the below results which is also a table in Teradata (i can also use a sqloverride in source qualifier)

 S_ID      A_ID     START_DATE          END_DATE      STATUS

   1          a         01-02-2017         01-03-2017     CLOSED

   1          a         01-03-2017         01-04-2017     CLOSED       

   1          b         01-04-2017         01-05-2017     CLOSED

   1          c         01-05-2017         31-12-9999     ACTIVE
Solution

  • Just use an expression transformation to feed the END_DATE which is sent out i.e. using an IIF statement in the END_DATE_OUT port: 

    IIF(STATUS = 'CLOSED', ADD_TO_DATE(START_DATE, 'MM', 1), END_DATE)