Here is the scenario:
You have multiple invoices in a table, with one record everytime the Invoice goes through a process, for example Invoice Created, Invoice Submitted, Invoice Approved etc. On top of that there could be multiple records for a step, for example an Invoice could be approved by multiple approvers, and that will end up being new records in the transaction history table.
If you need to create a report, that will show list of Invoices approved, and name of the last approver and the date, though could be tricky to get. After doing some research I found a simple way of determining that programatically:
We select the invoice along with timestamp of record, and sort them in descending order. We also select row_number for this query. The trick is to filter this result set where row_number is 1. Here is the exact sql if you need to refer:
Select User_Id, approve_date, Invoice_Id From (
Select Sth.User_Id user_id, Sth.Timestamp approve_date, Swpv.Property_Value Invoice_Id,
Row_Number() Over (Partition By Swpv.Property_Value Order By To_Char(Sth.Timestamp,'DD-MON-YYYY HH24:MI:SS') Desc) R
From Sf_Transition_History sth, Sf_Workitem_Property_Values_V swpv
Where Sth.Workitem_Instance_Id = swpv.Workitem_Instance_Id
And Sth.Event_Name = 'Approved'
And Swpv.Property_Name = 'InvoiceID'
)
Where R = 1
