I need to add a date/time stamp to a task name when status is complete in Performance

Sherie Ross

I have a performance analysis that includes the fields: "- Performance Task"."Manager Task Name", "- Performance Task"."Worker Task Name" and then it is pivoted so these tasks become column headings and then I can see the task status. I need to add the date/time stamp for just one of these tasks, but I have not been able to find a way to do that. I found idea 679918 from 2023 that seems to address this issue, but I don't see that anything has been done. I need to know if there is a possible work-around so I can get this date/time stamp to show on my analysis.

THIS IS WHAT WORKS WITHOUT THE DATE/TIME STAMP:

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT

0 s_0,

CAST(NULL AS VARCHAR(1)) s_1,

"Workforce Performance - Performance Task Status Real Time"."- Performance Task"."Performance Task Sequence Number" s_2,

"Workforce Performance - Performance Task Status Real Time"."Department"."Department Name" s_3,

"Workforce Performance - Performance Task Status Real Time"."Department"."PER_ORGANIZATION_UNIT_DFF_UT_EC_LEVEL_DFF_v" s_4,

"Workforce Performance - Performance Task Status Real Time"."Job"."Job Name" s_5,

"Workforce Performance - Performance Task Status Real Time"."Performance Document Details"."Performance Document Name" s_6,

"Workforce Performance - Performance Task Status Real Time"."Performance Document Details"."Performance Document Status" s_7,

"Workforce Performance - Performance Task Status Real Time"."Worker"."Assignment Status" s_8,

"Workforce Performance - Performance Task Status Real Time"."Worker"."Person Number" s_9,

CAST(NULL AS VARCHAR(1)) s_10,

DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."- Performance Task Status Details"."Performance Task Status") s_11,

DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."Job"."Job Name") s_12,

DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."Worker"."Assignment Status") s_13,

IFNULL("Workforce Performance - Performance Task Status Real Time"."- Performance Task"."Manager Task Name","Workforce Performance - Performance Task Status Real Time"."- Performance Task"."Worker Task Name") s_14,

CASE WHEN "Workforce Performance - Performance Task Status Real Time"."- Performance Task Status Details"."Performance Task Status" ='Completed' THEN COUNT(DISTINCT "Workforce Performance - Performance Task Status Real Time"."Worker"."Person Number") ELSE 0 END s_15,

REPORT_MAX("Workforce Performance - Performance Task Status Real Time"."- Performance Task Status Details"."Performance Task Status" BY "Workforce Performance - Performance Task Status Real Time"."Worker"."Person Number",DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."Worker"."Assignment Status"),"Workforce Performance - Performance Task Status Real Time"."Department"."Department Name","Workforce Performance - Performance Task Status Real Time"."Department"."PER_ORGANIZATION_UNIT_DFF_UT_EC_LEVEL_DFF_v",DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."Job"."Job Name"),"Workforce Performance - Performance Task Status Real Time"."- Performance Task"."Performance Task Sequence Number",IFNULL("Workforce Performance - Performance Task Status Real Time"."- Performance Task"."Manager Task Name","Workforce Performance - Performance Task Status Real Time"."- Performance Task"."Worker Task Name"),"Workforce Performance - Performance Task Status Real Time"."Performance Document Details"."Performance Document Name") s_16

FROM "Workforce Performance - Performance Task Status Real Time"

WHERE

((DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."Worker"."Assignment Status Type") = 'ACTIVE') AND ("Document Type"."Document Type Name" = 'Annual Review') AND (DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."- Performance Task Status Details"."Performance Task Status") IN ('COMP', 'INPROG', 'READY', 'SUBMITTED')) AND ("Department"."PER_ORGANIZATION_UNIT_DFF_UT_EC_LEVEL_DFF_v" <> 'B6000 - Human Resources') AND ("- Performance Task"."Performance Task Sequence Number" IN (10, 20, 30)) AND (IFNULL("- Performance Task"."Manager Task Name", "- Performance Task"."Worker Task Name") IN ('Employee Final Comments and Acknowledgements', 'Employee Self-Evaluation', 'Manager Evaluation of Employee')))

ORDER BY 4 ASC NULLS LAST, 10 ASC NULLS LAST, 9 ASC NULLS LAST, 14 ASC NULLS LAST, 6 ASC NULLS LAST, 13 ASC NULLS LAST, 7 ASC NULLS LAST, 5 ASC NULLS LAST, 3 ASC NULLS LAST, 15 ASC NULLS LAST

FETCH FIRST 250001 ROWS ONLY

I’VE TRIED TO ADD A COLUMN FOR DATE/TIME STAMP AND THEN JUST CHANGE ‘COMPLETED’ TO THE DATE/TIME STAMP:

SOMETHING LIKE THIS:

CASE

WHEN "- Performance Task"."Worker Task Name") = 'Employee Final Comments and Acknowledgements'

THEN CAST("- Performance Task Status Details"."Step Completion Date" AS CHAR)

ELSE IFNULL("- Performance Task"."Manager Task Name", "- Performance Task"."Worker Task Name")

END