SQL可以计算今天和结束日期之间的天数，然后根据天数创建一个红色，琥珀色，绿色状态列

Question

我有某些任务的起点和结束日期，请注意 task_scheduled_end_date 可能会填充或可能被填写：

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

我想：

• 首先计算

  - 今天_date ）

• 设置新列 red_amber_green to red，amber green，amber green或基于逻辑的未知：

   如果`task_days_due`＆lt; = 0显示红色  
  如果`task_days_due`在1到7之间显示琥珀色
  如果`task_days_due`是＆gt; 7显示绿色
  如果无法计算`task_days_due`（null中的task_scheduled_end_date）显示未知
   

理想输出是：

   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

当前Impala SQL，请注意 netatediff

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

我的尝试 > red_amber_green ，但不确定在选择中或之后将IE放置在哪里：

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

您可以在内部查询中计算日期差异，然后在外部计算red_amber_green标志。

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

请注意，我认为您的结束日期是时间戳。如果不是，则可以使用 to_timestamp（scheduled_end_date，'dd/mm/yyyy hh：mm'）将字符串转换为Timestamp。不要转换为不需要的东西，例如int。