I Need your help regarding this case statement that has be stumped.

I have a field called "activity_end_date_time". This field is a date/time field i am trying to write a simple case statement to say

1. If there is activity in the last 3 years(from todays data) then "3 years"
2. If there is activity more than last 3 years(from todays data) then "More 3 years"
3. If there is no activity i.e. null then "Null"

My thinking is

Todays date = 26/01/2023- 1095 days (3 years) = 27/01/2020 anything prior to 27/01/2020 should be "More than 3 years"

However i have examples where Last_Activity_End_Date is "2018-12-01", however my case statement is returning "3 Years"

This is my case statement

case 
when Last_Activity_End_Date  < Dateadd(year,3,Last_Activity_End_Date ) then '3 Years'
when Last_Activity_End_Date  >= Dateadd(year, 3,Last_Activity_End_Date ) then 'More than 3 Years'
when Last_Activity_End_Date IS null THEN 'NULL'
end as "Last_Activity_Identifer"

Looking forward to your help

CodePudding user response：

Because Amazon Redshift is based on PostgreSQL, you can try to use PostgreSQL function age in next way:

select 
    case
        when age(Last_Activity_End_Date) <  '3 year' then '3 Years' 
        when age(Last_Activity_End_Date) >= '3 year' then 'More than 3 Years'
    end as "Last_Activity_Identifer"; 

test it online: https://sqlize.online/s/lt

CodePudding user response：

Your logic is comparing the column to itself. You need to compare to 3 year before today's date. Using your structure and comparison operators:

case 
when getdate()  < Dateadd(year,3,Last_Activity_End_Date ) then '3 Years'
when getdate()  >= Dateadd(year,3,Last_Activity_End_Date ) then 'More than 3 Years'
when Last_Activity_End_Date IS null THEN 'NULL'
end as "Last_Activity_Identifer"

This is untested but should work and shows the logic needed for the comparision.