Hogpen,
Next time it would be helpful to present the data like this, then we could easily duplicate the data and write the queries:
CREATE TABLE Accounting
(ProdDate DateTime,
AcctDate DateTime,
MtrNo int,
MtrSfx char(1),
TrnxID int,
RecCode char (2)
)
insert into Accounting
select '2/1/2011','8/1/2011','365','A','8301','RR' union all
select '2/1/2011','2/1/2013','365','A','8301','RR' union all
select '2/1/2011','2/1/2011','365','A','8301','OR' union all
select '3/1/2011','9/1/2011','365','A','8302','RR' union all
select '3/1/2011','3/1/2013','365','A','8302','RR' union all
select '3/1/2011','3/1/2011','365','A','8302','OR'
Here is the delete statement that I've come up with:
DELETE Accounting
FROM Accounting a,
(SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate) AcctDate
FROM Accounting
WHERE RecCode = 'RR'
GROUP BY MtrNo, MtrSfx, TrnxID, RecCode
HAVING COUNT(TrnxID) > 1) b
where a.MtrNo = b.MtrNo and a.MtrSfx = b.MtrSfx
and a.TrnxID = b.TrnxID and a.RecCode = b.RecCode
and a.AcctDate = b.AcctDate
You need to group the data by all the primary key fields and then join that data on the original table and select. I find it is always helpful to write my code as a delete and just before I delete it, run it as a select to make sure I have the right code:
select a.*
FROM Accounting a,
(SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate) AcctDate
FROM Accounting
WHERE RecCode = 'RR'
GROUP BY MtrNo, MtrSfx, TrnxID, RecCode
HAVING COUNT(TrnxID) > 1) b
where a.MtrNo = b.MtrNo and a.MtrSfx = b.MtrSfx
and a.TrnxID = b.TrnxID and a.RecCode = b.RecCode
and a.AcctDate = b.AcctDate
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455