Swapping one column to another

There are n number of parent tasks and each task has their actual start time and actual end time, now there are child task as well, for one parent task which ends with ‘createprovision’ i want to replace the end time with the start time of the child task which starts with ‘Preparations’

Sorry I am editing the question

SELECT DISTINCT PCD.CHANGE_ID PARENT_CHANGE_ID,ICT2.TASK_ID PARENT_TASK_ID  
, NULL as INFRA_CHANGE_ID, NULL as INFRA_TASK_ID, NULL as OPS_CAT3,  
ICT2.ACTIVATE_TIME TASK_ACTIVATE_TIME,  
ICT2.ACTUAL_END_DATE ASK_ACTUAL_END_TIME,  
ICT2.SEQUENCE PARENT_SEQ,  
NULL as INFRA_SEQ,  
ICT2.NAME  
--ROUND(Business_Hours(timezone_convert(ICT2.ACTUAL_START_DATE), timezone_convert(ICT2.ACTUAL_END_DATE)  
--,7,18,'Sat/Sun'),2) TASK_MTRS_IN_HRS  
FROM   
V_ITSM_REPORT_CHANGE_DATA PCD  
JOIN V_ITSM_REPORT_CHANGE_TASK PCT ON PCD.CHANGE_ID=PCT.CHANGE_ID  
LEFT JOIN V_ITSM_REPORT_CHANGE_RELATIONS CR ON PCD.CHANGE_ID=CR.REQUEST_ID  
LEFT JOIN V_ITSM_REPORT_CHANGE_DATA ICD ON ICD.CHANGE_ID=CR.CHANGE_ID  
JOIN V_ITSM_REPORT_CHANGE_TASK ICT ON ICT.CHANGE_ID=ICD.CHANGE_ID   
JOIN V_ITSM_REPORT_CHANGE_TASK ICT2 ON ICT2.CHANGE_ID = PCD.CHANGE_ID  
JOIN V_ITSM_REPORT_CHANGE_TASK_WI WI ON WI.TASK_ID = ICT.TASK_ID   
where 1=1  
AND PCD.CHANGE_ID = 'CRQ000001307652'  
GROUP BY  
PCD.CHANGE_ID,  
ICT2.TASK_ID,  
ICT2.ACTIVATE_TIME,  
ICT2.ACTUAL_END_DATE,  
ICT2.SEQUENCE,  
ICT2.NAME  
UNION  
SELECT DISTINCT PCD.CHANGE_ID PARENT_CHANGE_ID, NULL as PARENT_TASK_ID,  
ICD.CHANGE_ID INFRA_CHANGE_ID,ICT.TASK_ID INFRA_TASK_ID,  
ICD.OPS_CAT3 OPS_CAT3,  
ICT.ACTIVATE_TIME TASK_ACTIVATE_TIME,  
ICT.ACTUAL_END_DATE TASK_ACTUAL_END_TIME,  
NULL AS PARENT_SEQ,  
ICT.SEQUENCE INFRA_SEQ,  
ICT.NAME  
--ROUND(Business_Hours(timezone_convert(ICT.ACTUAL_START_DATE), timezone_convert(ICT.ACTUAL_END_DATE)  
--,7,18,'Sat/Sun'),2) TASK_MTRS_IN_HRS  
FROM   
V_ITSM_REPORT_CHANGE_DATA PCD  
JOIN V_ITSM_REPORT_CHANGE_TASK PCT ON PCD.CHANGE_ID=PCT.CHANGE_ID  
LEFT JOIN V_ITSM_REPORT_CHANGE_RELATIONS CR ON PCD.CHANGE_ID=CR.REQUEST_ID  
LEFT JOIN V_ITSM_REPORT_CHANGE_DATA ICD ON ICD.CHANGE_ID=CR.CHANGE_ID  
JOIN V_ITSM_REPORT_CHANGE_TASK ICT ON ICT.CHANGE_ID=ICD.CHANGE_ID   
JOIN V_ITSM_REPORT_CHANGE_TASK_WI WI ON WI.TASK_ID = ICT.TASK_ID   
where 1=1  
AND PCD.CHANGE_ID = 'CRQ000001307652'  
GROUP BY  
PCD.CHANGE_ID,  
ICD.CHANGE_ID,  
ICD.OPS_CAT3,  
ICT.TASK_ID,  
ICT.ACTIVATE_TIME,  
ICT.ACTUAL_END_DATE,  
ICT.SEQUENCE,   
ICT.NAME  
order by PARENT_SEQ, INFRA_SEQ  

The output is

I want to replace the actual end time of name column with value ‘slc-asset:global|win-global-createprovision‘ task with task activate time of the Preparation Provisioning virtual in the name column

Hope it make sense

Thanks

Rakesh

Answer

You have several options. Probably you could rewrite query to non-union version, but it’s too hard without data access and I cannot assist you with that. Second option is analitycal function:

select other_columns...,

       case name when 'slc-asset:global|win-global-createprovision' 
            then max(case when name = 'Preparation Provisioning virtual' 
                          then task_activate_time 
                     end) 
                 over (partition by parent_change_id)
            else task_actual_end_time
       end task_actual_end_time

  from (your_query)         

Third option is subquery in first part of union. If your table may contain more than one such row use min(task_activate_time) or find proper row which should be presented in such situation.

case when ict2.name = 'slc-asset:global|win-global-createprovision' 
     then (select activate_time 
             from v_itsm_report_change_task 
             where change_id = icd.change_id 
               and name = 'Preparation Provisioning virtual')
     else ict2.actual_end_date 
end task_actual_end_time

You use group by, then distinct, then union. These are all costly aggregations. I think you could remove distinct and change union to union all.