It's all about the answers!

Ask a question

Rational Insight Data Manager v1.0.1 RTC_WorkitemStateHistory query returns duplicate rows


Dave Decker (331516) | asked May 07 '14, 3:58 p.m.
We have workitems that have been in the same state on more than one occasion, and i'm seeing multiple records added to Request State History table in RI data ware house. Has this query ever been updated to select the correct "Previous" state change record?

SELECT H."FACT_ID",H."WI_ID",
CASE
WHEN (T1."{$TIMESTAMP_COL}"-MWI."CREATION_DATE")< 86400 THEN MWI."CREATION_DATE"
ELSE T1."{$TIMESTAMP_COL}"
END AS NEW_TIME,
MAX(T2."{$TIMESTAMP_COL}") AS OLD_TIME,
UPPER(S1."STATE_NAME") AS OLD_STATE,UPPER(S2."STATE_NAME") AS NEW_STATE, S1."STATE_GROUP" AS OLD_STATE_GROUP, S2."STATE_GROUP" AS NEW_STATE_GROUP
FROM {$WORKITEM_CHNGS} H
JOIN {$TIME_TBL} T1 ON H."TIME_ID"=T1."TIME_ID"
JOIN {$STATE_TBL} S1 ON H."PREV_WI_STATE_ID"=S1."{$STATE_COL}"
JOIN {$STATE_TBL} S2 ON H."WI_STATE_ID"=S2."{$STATE_COL}"
LEFT JOIN {$WORKITEM_CHNGS} HO
ON HO."WI_ID"=H."WI_ID"
AND HO."WI_STATE_ID"=H."PREV_WI_STATE_ID"
LEFT JOIN {$TIME_TBL} T2 ON HO."TIME_ID"=T2."TIME_ID"
LEFT JOIN "MODEL"."WORK_ITEM" MWI on H."WI_ID"=MWI."ID"
WHERE (H."PREV_WI_STATE_ID" IS NULL OR H."WI_STATE_ID"<>H."PREV_WI_STATE_ID") AND (HO."WI_ID" IS NULL OR (HO."WI_STATE_ID"<>HO."PREV_WI_STATE_ID" AND T2."{$TIMESTAMP_COL}"<T1."{$TIMESTAMP_COL}"))
GROUP BY H."WI_ID",H."FACT_ID",S1."STATE_NAME",S1."STATE_GROUP",S2."STATE_NAME",S2."STATE_GROUP",T1."{$TIMESTAMP_COL}",T2."{$TIMESTAMP_COL}",MWI."CREATION_DATE"

One answer



permanent link
Dave Decker (331516) | answered Jun 10 '14, 10:48 a.m.
 Update: Removing group by 'T2."{$TIMESTAMP_COL}" fixes this issue....

Comments
Francesco Chiossi commented Jun 10 '14, 11:46 a.m.

Thanks for sharing your solution, Dave!

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.