Skip to Content
avatar image
Former Member

Item Create Date Query

Hi All,

I want to create a query from the change log that will display the date and user that created an Item Master Data document. I pull the item and date fields from AITM table, but when I introduce the OUSR table for the user name I return one entry for every user in the system. Is there a different field I should be pulling for the user name associated with the first instance? Thanks!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 04, 2008 at 10:01 PM

    Hi Emily,

    Try the following query:

    ==============================

    SELECT T0.[ItemCode], T1.[U_NAME] as 'User that created Item', t0.updatedate

    FROM AITM T0 inner join OUSR T1 on t0.usersign = t1.internal_K

    WHERE T0.[ItemCode] = '[%0]'

    group by T0.[ItemCode], T1.[U_NAME], t0.loginstanc, t0.updatedate

    having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = '[%0]')

    =============================

    Please note that the editor will remove the square brackets around %0, you need to add them for the query to work.

    To get the user that last changed the item just change the 'min' to 'max'. I didn't manage to get them in the same query by using the username, if the user code is ok you can have both the creator and the last person that changed it in the same query.

    Hope it helps.

    Jesper

    Add comment
    10|10000 characters needed characters exceeded