SQL Server - need a SQL Query to identify/highlight specific changes in an audit table

Question

Say I have some data stored in an audit table, where triggers on the main data table write all invoice record updates to this audit table. The audit table contains this data:

InvoiceID CustomerID  ItemSold    AmountSold     SalesPerson  ModifyDate
1001      96          Widget      800            Robert       2001-1-1
1006      85          Thinger     350            Phil         2001-1-8
1001      96          Widget      800            Bobby        2001-1-9
1005      22          Widget      400            Robert       2001-1-10
1006      44          Thinger     500            Mike         2001-2-5
1001      96          Widget      250            Robert       2001-6-4

And I want to write a query which will identify whenever the SalesPerson field changes, for any particular InvoiceID (eg: whenever a salesman changes the sale to his name).

So in the example above, I'd like to identify the change which took place on 2001-1-9, where the sale for InvoiceID 1001 went from Robert to Bobby, and the change on 2001-6-4 where it went back to Robert from Bobby...so two changes for that particular ID. And I'd also like to identify the change on 2001-2-5 where the sale for InvoiceID 1006 went from Phil to Mike.

How can I write a SQL query which will identify/highlight these changes?

The table doesn't currently contain a primary key, but I can add one if needed.

Answer 1

If you add a primary key (which you should do, it will make some of the querying you need on this table easier in the long run) Then what you need is a self join. Something like this might do it:

select a.invoiceId, a.SalesPerson as FirstSalesPerson, 
    a.Modifydate as FirstModifyDate, b.SalesPerson as SecondSalesPerson, 
    B.Modifydate as SecondModifyDate  
from myaudittable a
join myadudittable b
   on a.InvoiceID = b.InvoiceID
where a.AuditIDd <>b.AuditID and a.ModifyDate < b.ModifyDate
   and a.SalesPerson<>b.SalesPerson
order by InvoiceID

Answer 2

This should do it.

declare @Audit table (
    InvoiceID int,
    CustomerID int,
    ItemSold varchar(10),
    AmountSold int,
    SalesPerson varchar(10),
    ModifyDate datetime
)

insert into @Audit
    (InvoiceID, CustomerID, ItemSold, AmountSold, SalesPerson, ModifyDate)
    values
    (1001, 96, 'Widget', 800, 'Robert', '2001-1-1'),
    (1006, 85, 'Thinger', 350, 'Phil', '2001-1-8'),
    (1001, 96, 'Widget', 800, 'Bobby', '2001-1-9'),
    (1005, 22, 'Widget', 400, 'Robert', '2001-1-10'),
    (1006, 44, 'Thinger', 500, 'Mike', '2001-2-5'),
    (1001, 96, 'Widget', 250, 'Robert', '2001-6-4')

select a2.InvoiceID, a2.SalesPerson, a2.ModifyDate
    from @Audit a1
        inner join @Audit a2
            on a1.InvoiceID = a2.InvoiceID
                and a1.ModifyDate < a2.ModifyDate
                and a1.SalesPerson <> a2.SalesPerson

Answer 3

Here's a more complete answer, I think. It assumes:

1. at least SQL Server 2005
2. that the ModifyDate column is the time at which the record is created in the audit log.
3. the existence of an identity primary key, AuditID

---

declare @Audit table 
(
    AuditID int identity(1,1),
    InvoiceID int,
    CustomerID int,
    ItemSold varchar(10),
    AmountSold int,
    SalesPerson varchar(10),
    ModifyDate datetime
)

;with orders (InvoiceID, SalesPerson, ModifyDate, idx)
as
(
    select 
        InvoiceID, 
        SalesPerson, 
        ModifyDate, 
        row_number() over (partition by InvoiceID order by AuditID desc)
    from @Audit
) 

select o2.InvoiceID, o2.SalesPerson, o2.ModifyDate from orders o1 inner join orders o2 
on 
    o1.InvoiceID = o2.InvoiceID and 
    o1.SalesPerson <> o2.SalesPerson and
    o1.idx = o2.idx-1
order by InvoiceID, ModifyDate desc

Answer 4

I used some bits and pieces from the posted answers, but the only way I was able to isolate the actual changes in salesperson was to use a subquery. Otherwise I was getting too many results and it was difficult to isolate the actual dates that the record changed salespersons.

 select InvoiceId,SalesPerson,auditdate from myaudittable where InvoiceId in 
 (select distinct a.InvoiceId 
 from myaudittable a inner join myaudittable b on a.InvoiceId = b.InvoiceId and         
 a.SalesPerson <> b.SalesPerson)                
 group by InvoiceId,SalesPerson