I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm attempting the following:
CASE WHEN 'AmortDate' IS NOT NULL THEN 1
ELSE 0
END AS "IsAmortizing?'
I've tried several different variations ( NULL, '', IS NOT NULL)
CASE WHEN ln.PAYMENT_SCHEDULE_TYPE = '0' THEN ''
WHEN ln.PAYMENT_SCHEDULE_TYPE = '1' THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '0' AND ln.PAYMENT_SCHEDULE_NBR = '1' AND lpsf.psDatePmtDueFirst ''
THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '0' AND ln.PAYMENT_SCHEDULE_NBR = '1' AND lpsf.psDatePmtDueFirst = ''
THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120)
WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '999' AND ln.PAYMENT_SCHEDULE_NBR = '1' THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE = '0' AND ln.PAYMENT_SCHEDULE_AMOUNT = '0'
THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE IN('1','2','3') AND ln.PAYMENT_SCHEDULE_AMOUNT = '0'
THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE IN('1','2','3') AND ln.PAYMENT_SCHEDULE_AMOUNT > 0
THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE = '6' AND ln.PAYMENT_SCHEDULE_AMOUNT = '0'
THEN CONVERT(VARCHAR(10),ln.DATE_MATURITY_NEXT,120)
ELSE ''
END AS AmortDate
Based off of the results of the derived AmortDate field above, I need to create another calculated field:
CASE WHEN AmortDate IS NOT NULL THEN 1
ELSE 0
END AS 'IsAmortizing'
Asked by Daylon Hunt
(189 rep)
Dec 13, 2019, 05:55 PM
Last activity: Dec 16, 2019, 08:27 PM
Last activity: Dec 16, 2019, 08:27 PM