Item Group Change

[Former Member]

Hi there, I'm trying to build a query that identifies changes on item group this year.

I've got the data from table AITM and so far the query looks like below. The issue that I'm having is that the UpdateDate is picking up the whole table changes (and not only the item group code changes). Like I said as a result I want to see all the items that had the item group changed in the current year. Does anyone have any idea on how to get it?

Thank you!

SELECT DISTINCT
T0."Code", T0."Name", T0."GroupCode", T1."GroupCode" , T0."UpdateDate"

FROM (select "Code", "Name", "GroupCode", "UpdateDate" FROM AITM) T0

INNER JOIN (select "Code", "Name", "GroupCode", "UpdateDate" FROM AITM) T1 ON T0."Code" = T1."Code"

WHERE T0."GroupCode" <> T1."GroupCode" AND

DAYS_BETWEEN( T0."UpdateDate", CURRENT_DATE) <= 80

[SergioG_TX]

if you are needing to only get the changes in the past rolling year.... then your where clause has to be...

select code, name, groupcode, updatedate from aitm where "UpdateDate" > add_months(NOW(), -12); -- so you get the updates greater than the past 12 months.

you should not join the code column as code/group code has the same value when you do your inner join to itself. unless you are comparing 2 diff columns to have different values

[Former Member]

The reason why I joined the table to itself is to identify differences in the group code for one item, that part works fine.

The part that is not working is that the table AITM contains the history of different changes done in one item and therefore when I try to capture the time it's picking up anything that has changed (not only the group code change).