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 | +-----------+----------+----------+