Rational Insight Data Manager v1.0.1 RTC_WorkitemStateHistory query returns duplicate rows
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"
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"