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.
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
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
Here's a more complete answer, I think. It assumes:
- at least SQL Server 2005
- that the ModifyDate column is the time at which the record is created in the audit log.
- 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
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