Calculate Days Between Based on 3 Variables & Syntax Error

I’m hoping you can provide some insights on a couple of issues:

ISSUE 1 – i’m receiving the following error message running in Teradata: “Syntax error, expected something like ‘)’ between an integer and the ‘then’ keyword.”

There are some duplicate records in my data and what I am trying to say is if all conditions are met, then Age in Days equals 1, otherwise, calculate the days.

ISSUE 2 – the date_diff is not calculating correctly (see table)

Date_Phase_Modified	Historical_Phases	Date_Diff
1/4/2021 20:33	Closed	shows 0 – want to see 1
1/4/2021 20:32	Implement	63 – this is correct
12/1/2020 18:34	Implement	shows 29 – want to see 0
11/2/2020 18:20	Implement	0 – this is correct
10/21/2020 20:07	Early Stage	shows 0 – want to see 1
10/21/2020 20:06	Early Stage	0 – this is correct
10/21/2020 19:59	Early Stage	0 – this is correct

CASE when((
MAX(cast(Date_Phase_Modified as date format 'MM/DD/YYYY')) - MIN(cast(Date_Phase_Modified as date format 'MM/DD/YYYY')) 
            over (partition by F.oprty_key_nbr, Historical_Phases order by Historical_Phases, Date_Phase_Modified asc) Day(4)) = 0 then 1 else 
            
MAX(cast(Date_Phase_Modified as date format 'MM/DD/YYYY')) - MIN(cast(Date_Phase_Modified as date format 'MM/DD/YYYY')) 
            over (partition by F.oprty_key_nbr, Historical_Phases order by Historical_Phases, Date_Phase_Modified asc) Day(4)) 

end as Age_In_Phase,

Answer

This seems to match your expected result:

CASE 
  WHEN Date_Phase_Modified = -- for latest row per phase
                      Max(Date_Phase_Modified) Over (PARTITION BY F.oprty_key_nbr, Historical_Phases ) 
  THEN -- return duration of phase, but at least 1 day
       Greatest( Cast(Max(Date_Phase_Modified) Over (PARTITION BY F.oprty_key_nbr, Historical_Phases ) AS DATE)
                -Cast(Min(Date_Phase_Modified) Over (PARTITION BY F.oprty_key_nbr, Historical_Phases ) AS DATE)
               , 1) 
  ELSE 0
END AS Age_In_Phase