SQL to Calculate days between Today and an End Date, then create a Red, Amber, Green status column based on days until task is due

Question

I have a start and end date of some tasks due, note task_scheduled_end_date may or maybe be populated:

task_scheduled_start_date   Today_Date  task_scheduled_end_date 
14/07/2022 14:00            26/06/2022  NULL
9/06/2022 23:00             26/06/2022  13/10/2022 23:00

I would like to:

• First calculate Task_Days_Due (task_scheduled_end_date- Today_Date)

• Set new column Red_Amber_Green to Red, Amber Green or Unknown based on logic:

  If `Task_Days_Due` <= 0 show Red  
  If `Task_Days_Due` is between 1 and 7 show Amber
  If `Task_Days_Due` is > 7 show Green
  If `Task_Days_Due` Cannot be calculated (task_scheduled_end_date in NULL) show Unknown
  

Ideal output is:

   task_scheduled_start_date    Today_Date  task_scheduled_end_date Task_Days_Due Red_Amber_Green
    14/07/2022 14:00            26/06/2022  NULL                    Unknown       Unknown
    9/06/2022 23:00             26/06/2022  13/10/2022 23:00        109           Green
    9/06/2022 23:00             26/06/2022  25/06/2022 23:00        -1            Red
    9/06/2022 23:00             26/06/2022  27/06/2022 23:00         1            Amber

Current Impala SQL, note there is a syntax error on DATEDIFF

SELECT          hpd_help_desk.incident_number,                   
                hpd_associations.request_id01 AS "PBI",
                tms_task.task_id,
                --tms_task.scheduled_start_date,
                from_unixtime(Cast(tms_task.scheduled_start_date AS BIGINT),'yyyy-MM-dd HH:mm:ss') AS "Task_Scheduled_Start_Date",
                --tms_task.scheduled_end_date, 
                from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss') AS "Task_Scheduled_End_Date",
                CURRENT_DATE() AS "Task_Today_Date",
                DATEDIFF(TO_DATE( CURRENT_DATE()), TO_DATE(from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss') AS "Task_Scheduled_End_Date")) AS "Task_Days_Due",
                --Task_Red_Amber_Breen?
                        
FROM   helix_access.hpd_help_desk
       LEFT OUTER JOIN helix_access.hpd_associations
                    ON ( hpd_help_desk.incident_number = hpd_associations.request_id02)
       LEFT OUTER JOIN helix_access.tms_task
                    ON ( hpd_associations.request_id01 = tms_task.rootrequestid)
                    
WHERE   hpd_help_desk.incident_number = 'INC000038006072'
order by incident_number

My attempt at Red_Amber_Green, but not sure where to place i.e. in the select or after:

update Red_Amber_Green
set Red_Amber_Green= (CASE 
                      WHEN Task_Days_Due <= 0 
                        THEN 'Red'
                      WHEN Task_Days_Due > 0 and Task_Days_Due <= 7
                        THEN 'Amber'
                      WHEN Task_Days_Due > 7 
                        THEN 'Green'
                      ELSE
                        'Unknown'  
                    END);

Answer 1

can you calculate date diff in inner query and then calculate red_amber_green flag outside.

select
case when Task_Days_Due <= 0  THEN 'Red'
     WHEN Task_Days_Due > 0 and Task_Days_Due <= 7 THEN 'Amber'
     ... as Red_Amber_Green
end  --outerquery to calculate the flag
FROM 
(select ...
 DATEDIFF(now()), tms_task.scheduled_end_date) AS "Task_Days_Due" --inner query to calculate date diff
from...
)rs

Pls note i assumed your end date is a timestamp. if not, you can use to_timestamp(scheduled_end_date,'dd/MM/yyyy HH:mm') to convert string to timestamp. Do not convert to something thats not needed like int.