I have a query that will create a report (Excel spreadsheet) and now I need to add two columns: "2017 DNR" and "2018 DNR" to the report. There is a column in the table named DNRDATE (format dd-MON-yy) and if it is NULL, I need both columns to say "NO" in the report. If there is a DNRDATE, need to check if that date is 2017 or 2018. If it's 2017, then both columns get a "YES". If it's 2018, then "2017 DNR" gets a "NO" and 2018 gets a "YES."
Should I use a CASE WHEN for this? And how would that work?
SELECT CASE WHEN to_char(dnrdate,'YYYY') = '2017' THEN 'YES' ELSE 'NO' END "2017 DNR",
CASE WHEN to_char(dnrdate,'YYYY') = '2018' THEN 'YES' ELSE 'NO' END "2018 DNR"
FROM ...
Do not worry about special logic for NULL values. If DNRDATE IS NULL, then both of those expressions will return "NO".
Full Example:
WITH test_data (dnrdate) AS (
SELECT to_date('05-JUN-2017','DD-MON-YYYY') FROM DUAL UNION ALL
SELECT to_date('05-JUN-2018','DD-MON-YYYY') FROM DUAL UNION ALL
SELECT NULL FROM DUAL )
SELECT dnrdate,
CASE WHEN to_char(dnrdate,'YYYY') = '2017' THEN 'YES' ELSE 'NO' END "2017 DNR",
CASE WHEN to_char(dnrdate,'YYYY') = '2018' THEN 'YES' ELSE 'NO' END "2018 DNR"
FROM test_data
+-----------+----------+----------+ | DNRDATE | 2017 DNR | 2018 DNR | +-----------+----------+----------+ | 05-JUN-17 | YES | NO | | 05-JUN-18 | NO | YES | | | NO | NO | +-----------+----------+----------+