Maximo Open Forum

 View Only

 SQL Select First Approver PO and Return PERSON associated with that Change

  • Administration
  • Analytics
  • Procurement
Adam Olson's profile image
Adam Olson posted 06-15-2024 12:22

I'm having difficulty getting a sql statement written that will allow me to return the Minimum Date from the PO Status Table.

I'm trying to return the First Approval Date of a PO and also return the CHANGEBY user that is associated with that specific approval.  I'm able to successfully get the First Approval date of the record, but when I introduce the CHANGEBY into the group by portion of the clause it's returning multiple rows because there a scenarios where there may be different approvers in the table.

Example:

ID PONUM REVISIONNUM CHANGEBY CHANGEDATE STATUS
1 PO1234 0 PERSONA 1/5/18 6:41 PM WAPPR
2 PO1234 0 PERSONA 1/5/18 6:42 PM READY
3 PO1234 0 PERSONB 1/5/18 6:43 PM APPR First Approval I want
4 PO1234 0 PERSONB 1/5/18 6:43 PM PRINT
5 PO1234 0 PERSONA 1/5/18 6:45 PM APPR
6 PO1234 0 MAXADMIN 1/8/18 10:00 AM CLOSE

SELECT
POSTATUS.PONUM
,POSTATUS.CHANGEBY
,MIN(POSTATUS.CHANGEDATE) APPR_DATE
FROM POSTATUS
WHERE POSTATUS.PONUM = 'PO1234' AND REVISIONNUM = '0' AND POSTATUS.STATUS = 'APPR'
GROUP BY
POSTATUS.PONUM
,POSTATUS.CHANGEBY

The statement above will return ID's 3 and 5 because of the group by.

Sid Ogden's profile image
Sid Ogden posted 06-17-2024 10:47

If I understand you correctly, I've modified your sql to the following. I've used BEDFORD data in an bare Maximo instance, so you'll have to substitue your PONUM in where I've got 1070CS.

select ponum, changeby, changedate APPR_DATE
from postatus
where changedate = (
select min(changedate) from postatus
where POSTATUS.PONUM = '1070CS'
and status = 'APPR'
and revisionnum = '0')
Martin Fabra's profile image
Martin Fabra posted 06-17-2024 12:00

Hi @Adam Olson! I give you this query that may solve your need.

SELECT 
    po.ponum, 
    po.revisionnum, 
    po.orgid,
    po.siteid,
    po.status,
    po.changedate,
    po.changeby,
    (SELECT MAX(pst.changedate)
     FROM postatus pst
     WHERE pst.ponum = po.ponum
       AND pst.orgid = po.orgid
       AND pst.siteid = po.siteid
       AND pst.status = 'APPR') AS last_appr,
    (SELECT pst2.changeby
     FROM postatus pst2
     WHERE pst2.ponum = po.ponum
       AND pst2.orgid = po.orgid
       AND pst2.siteid = po.siteid
       AND pst2.status = 'APPR'
       AND pst2.changedate = (SELECT MAX(pst3.changedate)
                              FROM postatus pst3
                              WHERE pst3.ponum = po.ponum
                                AND pst3.orgid = po.orgid
                                AND pst3.siteid = po.siteid
                                AND pst3.status = 'APPR')) AS last_ch_appr
FROM 
    po
ORDER BY 
    po.ponum, po.orgid, po.siteid;

Regards!

gail smith's profile image
gail smith posted 06-17-2024 13:16

TRY:

SELECT TOP 1
POSTATUS.PONUM
,POSTATUS.CHANGEBY
,POSTATUS.CHANGEDATE APPR_DATE
FROM POSTATUS
WHERE POSTATUS.PONUM = 'PO1234' AND REVISIONNUM = '0' AND POSTATUS.STATUS = 'APPR'
ORDER BY POSTATUS.CHANGEDATE

Related Content