Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

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"

0 votes



One answer

Permanent link
 Update: Removing group by 'T2."{$TIMESTAMP_COL}" fixes this issue....

1 vote

Comments

Thanks for sharing your solution, Dave!

Your answer

Register or log in 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.

Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details

Question asked: May 07 '14, 3:58 p.m.

Question was seen: 4,890 times

Last updated: Jun 10 '14, 11:46 a.m.

Confirmation Cancel Confirm