Search code examples
sqlconditional-statementscase

SQL to update a Select Statement column based on another Columns value but only if condition is met

I have a select statement and would like to update Task_Days_Due to a string (N/A - Task is Closed) if column task_status2 value is Closed Currently it shows the number of days until due but this is irrelevant if clised.

enter image description here

Here is the current select:

SELECT

    Report_Run_Date,
    incident_number,
    INC_Reported_Date,
    TASK_id,
    TASK_Assigneee, 
    TASK_Status, -- Staged 1000, Assigned 2000, Pending 3000, Work In Progress 4000, Waiting 5000, Closed 6000, ByPassed 7000
    Task_Status2,
    -- Set RAG column to RAG is not closed
    case    WHEN TASK_Status = 6000  THEN "N/A - Task is Closed"
            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'
            WHEN Task_Days_Due is NULL THEN 'No Task End Date'
    end     as Red_Amber_Green,
    Task_Days_Due, ?????

My challenge is I want to leave the value as is if the condition is not met. previous attempts are overwriting the value to null if column task_status2 value was not Closed

Solution 
  • SELECT

    Report_Run_Date,
    incident_number,
    INC_Reported_Date,
    TASK_id,
    TASK_Assigneee, 
    TASK_Status, -- Staged 1000, Assigned 2000, Pending 3000, Work In Progress 4000, Waiting 5000, Closed 6000, ByPassed 7000
    Task_Status2,
    -- Set RAG column to RAG is not closed
    case    WHEN TASK_Status = 6000  THEN "N/A - Task is Closed"
            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'
            WHEN Task_Days_Due is NULL THEN 'No Task End Date'
    end     as Red_Amber_Green,
    decode(task_status2, 'Closed', 'N/A - Task is Closed', cast(task_days_due as STRING)) as task_days_due