[SOLVED] How to build table with history - who finished the step

0

Chat GPT generated 2 codes:

1. for Data Table
[code]
WITH RankedHistory AS (
SELECT
WFH_ID AS ID_of_history,
WFH_TransitionReason,
WFH_STPID AS Krok_ID,
WFH_FinishDurationDateTime AS Data_ukończenia_kroku,
WFH_LeaveCurrentStepPath,
WFH_LeaveCurrentStepDate,
WFH_IsLastVersionInCurrentStep,
WFH_Impersonator,
dbo.clearwfelem(WFH_LeftBy) AS Zatwierdzone_przez,
dbo.clearwfelem(WFH_LeftByImpersonator) AS W_Zastępstwie,
FORMAT(
CASE
WHEN WFH_LeaveCurrentStepDate IS NULL THEN WFH_FinishDurationDateTime
ELSE WFH_LeaveCurrentStepDate
END, 'dd.MM.yyyy HH:mm:ss'
) AS Data_akceptacji,
ROW_NUMBER() OVER (
PARTITION BY dbo.clearwfelem(WFH_LeftBy)
ORDER BY
CASE
WHEN WFH_LeaveCurrentStepDate IS NULL THEN WFH_FinishDurationDateTime
ELSE WFH_LeaveCurrentStepDate
END DESC
) AS RowNum
FROM
WFHistoryElements
WHERE
WFH_OrgID = {WFD_ID}
AND WFH_STPID = {ST:369}
AND WFH_IsLastVersionInCurrentStep <> ''
)
SELECT
ID_of_history,
WFH_TransitionReason,
Krok_ID,
Data_ukończenia_kroku,
Zatwierdzone_przez,
W_Zastępstwie,
Data_akceptacji
FROM
RankedHistory
WHERE
RowNum = 1
ORDER BY
Data_akceptacji DESC;
[/code]

2 for action:
[code]
SELECT
WFH_ID AS ID_of_history,
WFH_TransitionReason,
WFH_STPID AS Krok_ID,
WFH_FinishDurationDateTime AS Data_ukończenia_kroku,
WFH_LeaveCurrentStepPath,
WFH_LeaveCurrentStepDate,
WFH_IsLastVersionInCurrentStep,
WFH_Impersonator,
dbo.clearwfelem(WFH_LeftBy) AS Zatwierdzone_przez,
dbo.clearwfelem(WFH_LeftByImpersonator) AS W_Zastępstwie,
FORMAT(
CASE
WHEN WFH_LeaveCurrentStepDate IS NULL THEN WFH_FinishDurationDateTime
ELSE WFH_LeaveCurrentStepDate
END, 'dd.MM.yyyy HH:mm:ss'
) AS Data_akceptacji
FROM
(SELECT
WFH_ID,
WFH_TransitionReason,
WFH_STPID,
WFH_FinishDurationDateTime,
WFH_LeaveCurrentStepPath,
WFH_LeaveCurrentStepDate,
WFH_IsLastVersionInCurrentStep,
WFH_Impersonator,
WFH_LeftBy,
WFH_LeftByImpersonator,
ROW_NUMBER() OVER (
PARTITION BY dbo.clearwfelem(WFH_LeftBy)
ORDER BY
CASE
WHEN WFH_LeaveCurrentStepDate IS NULL THEN WFH_FinishDurationDateTime
ELSE WFH_LeaveCurrentStepDate
END DESC
) AS RowNum
FROM WFHistoryElements
WHERE
WFH_OrgID = {WFD_ID}
AND WFH_STPID = {ST:369}
AND WFH_IsLastVersionInCurrentStep <> ''
) AS RankedHistory
WHERE
RowNum = 1
ORDER BY
Data_akceptacji DESC;
[/code]

Hi Paweł,
at first i'd recommend using the default save button at the menu bar - it keeps the history more tidy, and doesn't trigger step leave / step enter.

Also, when assigning a task for multiple users, they all should just press some 'Approve'/'Disapprove' button, rather than 'Save'.
Save doesn't mean it's approved.

From my perspective those adjustments should make filtering out what is important easier. If approval is done only by 'Approve' path, then just filter out those paths.
Save seems like multi-purpose path at this moment and this leads to confusion both for users and future developers/maintainers.

For unfinished tasks you could join WFHistoryElements with WFElementTasks (it's a view), on WFH_ID = WFT_WFHID, this will give you also tasks which are assigned, but not finished.

So, are you looking for people who finished their tasks or not?

Hej, this is query for you:

select TSK_User, TSK_WFDID, TSK_WFHIDTaskFinished
from ActiveTasks
where tsk_WFDID = X

Webcon adds information to HistoryTask once a job is completed by each user; however, this query returns details about the users who have completed their tasks, even if not all users have finished the task - you can check it by info in column TSK_WFHIDTaskFinished

Thank you,
I will play with this.