Deducting values from 2 case statements from one another

I have two CASE statements and I’m trying to deduct the value of one from another, but I can’t get the code to work. Does anyone have any suggestions where I’m going wrong?

The 2 CASE statements are:

case
    when "D570M"."END_DATE" IS NULL then TO_CHAR(LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY')))
    when "D570M"."END_DATE" > (LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY'))) then TO_CHAR (LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY')))
    else TO_CHAR("D570M"."END_DATE", 'DD-MON-YYYY')
end as "END DATE",

and

case
    when "D570M"."START_DATE"> LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY')) then TO_CHAR((LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY'))), 'DD-MON-YYYY')
    else TO_CHAR("D570M"."START_DATE", 'DD-MON-YYYY')
end as "START DATE",

When I try to combine these I get errors (missing right parenthesis). The combined code is:

SUM(
    (
        case
            when "D570M"."END_DATE" IS NULL then TO_CHAR(LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY')))
            when "D570M"."END_DATE" > (LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY'))) then TO_CHAR (LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY')))
            else TO_CHAR("D570M"."END_DATE", 'DD-MON-YYYY')
        end as "END DATE"
    ) -
    (
        case
            when "D570M"."START_DATE"> LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY')) then TO_CHAR((LAST_DAY(TO_CHAR(ADD_MONTHS(sysdate, -1), 'DD-MON-YYYY'))), 'DD-MON-YYYY')
            else TO_CHAR("D570M"."START_DATE", 'DD-MON-YYYY')
        end as "START DATE")
    )

as “CALENDAR DAYS”,

Answer

Forpas is right about the aliases; you also want to remove all those TO_CHAR() calls. You want to be doing arithmetic with dates, not with strings of characters. I think you’re trying to remove the time portion of the date, and the function you want for that is TRUNC().

-- example data
with D570M as (select sysdate-60 as start_date, sysdate as end_date from dual)
-- query
select
        case
            when D570M.END_DATE IS NULL then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1)))
            when D570M.END_DATE > TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))) then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1)))
            else TRUNC(D570M.END_DATE)
        end as "END DATE",
        case
            when D570M.START_DATE> TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))) then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1)))
            else TRUNC(D570M.START_DATE)
        end as "START DATE",
SUM(
    (
        case
            when D570M.END_DATE IS NULL then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1)))
            when D570M.END_DATE > TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))) then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1)))
            else TRUNC(D570M.END_DATE)
        end
    ) -
    (
        case
            when D570M.START_DATE > TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))) then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1)))
            else TRUNC(D570M.START_DATE)
        end
    )) as "CALENDAR DAYS"
from d570m;

Output:

END DATE  START DATE CALENDAR DAYS
--------- ---------- -------------
31-DEC-19 08-NOV-19             53
1 row selected.