Creating a date field from two other fields

[#1]

Hi,

I have 4 possible dates that I want the script to select one of the 4 and place it into a new date column. I attempted the if statement below but keep getting -1 as the value for all my dates.

CHURN_CAL:

LOAD

  WRKF_KEY,

  IF(ACC_NEW_HIRE_FLG = 'Y','A',IF(SEP_ALL_FLG = 'Y','S')) as CHURN_FLG,

  date(IF(SEP_ALL_FLG = 'Y' AND END_DT < today(),END_DT,SEP_DT) OR

  IF(ACC_NEW_HIRE_FLG = 'Y' AND START_DT > ACC_STRT_DT, START_DT,ACC_STRT_DT),'MM/DD/YYYY') AS CHURN_DATE

Resident Tmp1;

[#2]

***Update***

I got the script below to work as desired but seems a bit clunky. Any suggestions?

CHURN_CAL:

     LOAD

            WRKF_KEY,

                 IF(ACC_NEW_HIRE_FLG = 'Y','A',IF(SEP_ALL_FLG = 'Y','S')) as CHURN_FLG,

                 date(IF(SEP_ALL_FLG = 'Y' AND date(END_DT) < today(),date(END_DT),

                      IF(SEP_ALL_FLG = 'Y' AND date(END_DT) > today(),date(SEP_DT),

                          IF(ACC_NEW_HIRE_FLG = 'Y',date(START_DT)))),'MM/DD/YYYY') AS CHURN_DATE

Resident Tmp1;

DROP Table Tmp1;

[swuehl]

I believe you don't need to format dates inside your if() statement, just keep the formatting of the result:

         date(

               IF(SEP_ALL_FLG = 'Y' AND  END_DT < today(),  END_DT,

                      IF(SEP_ALL_FLG = 'Y' AND  END_DT > today(), SEP_DT,

                          IF(ACC_NEW_HIRE_FLG = 'Y', START_DT )))

               ,'MM/DD/YYYY')

                                                                                                               AS CHURN_DATE