most recent owner audit trail
-
Hello all,
I need to create a view of two tables, a case table and an owner audit trail updated when a case is transferred. The view should show the case id and the most recent owner of that case.
The tables look like this:
case table
caseid dateCreated createdBy notes
------ ------------ ---------- ------
55555 2005-08-01 Joe create notes
audit trail table
caseid dateTransferred newOwner
------ --------------- ----------
55555 2005-08-02 Jane nOwn
55555 2005-08-03 j. IOWNIT
Thanks in advance for any advice provided,
-
select a1. caseid, a1.datetransferred, a1.newowner
from AuditTrail a1 join
(select a2.caseid, max(a2.datetransferred) DateXfrd
from AuditTrail a2
group by a2.caseid) a2
on a1.caseid = a2.caseid and a1.datetransferred = a2. DateXfrd
Should do it (untested). Don't see why the Case table is needed.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
We're probabely missing quite a few columns in that exemple
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply