Sample Header Ad - 728x90

Working with NULL Date Fields

-2 votes
1 answer
2800 views
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