More than one condition in CASE clause after renaming of column in SQL

Question

Status	begin_date	end_date
Active	2019-12-01	2020-09-01
Active	2019-04-01	NULL
Active	2019-06-01	2019-06-09
Inactive	2019-05-01	2020-08-01

The result column should contain:

• 'E2' when difference between begin_date and end_date is greater than 14 days or if end_date is NULL
• 'X' when difference between begin_date and end_date is less than or equal to 14 days and
• 'I2' if the status is inactive

I have written this condition out in a CASE statement which takes care of 'E2' and 'X' however it doesn't result in 'I2' when the status is inactive, instead it outputs 'E2'. How do i achieve this result as another condition in the same CASE statement.

Here's the code:

CASE 
    WHEN DATEDIFF (day, begin_date, end_date) > 14 THEN 'E2'
    WHEN end_date IS NULL THEN 'E2'
    WHEN DATEDIFF (day, begin_date, end_date) > 14 
         AND end_date IS NULL AND status = 'Inactive' THEN 'I2'
    ELSE 'X'
END AS Result 

The output I am getting:

Status	begin_date	end_date	result
Active	2019-12-01	2020-09-01	E2
Active	2019-04-01	NULL	E2
Active	2019-06-01	2019-06-09	X
Inactive	2019-05-01	2020-08-01	E2

Expected output:

Status	begin_date	end_date	result
Active	2019-12-01	2020-09-01	E2
Active	2019-04-01	NULL	E2
Active	2019-06-01	2019-06-09	X
Inactive	2019-05-01	2020-08-01	I2

Answer 1

You need to rearange your code

and the part where IS NULL and inanctove needs an Or and parenthesis

SELECT "Status", "begin_date", "end_date",
CASE
  WHEN (DATEDIFF (day, begin_date, end_date) > 14 
      OR end_date is NULL) and status = 'Inactive' THEN 'I2'   
   WHEN DATEDIFF (day, begin_date, end_date) > 14 THEN 'E2'
  WHEN end_date is NULL  THEN 'E2'
     
     ELSE 'X'
END AS Result 
FROM testdata

Status	begin_date	end_date	Result
Active	2019-12-01	2020-09-01	E2
Active	2019-04-01	null	E2
Active	2019-06-01	2019-06-09	X
Inactive	2019-05-01	2020-08-01	I2

fiddle