Wednesday, February 7, 2007

MS SQL server: Deleting Duplicate Records

I have a table tmPunchtimeSummary which contains a sum of employee's hours per day. The table contains some duplicates.


CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5


How can I write a delete statement to only delete the duplicates which in this case would be the 1st and 5th records?


CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)

delete t1
from tmPunchtimeSummary as t1 inner join tmPunchtimeSummary as t2
on t1.sCalldate = t2.sCalldate and t1.sEmployeeId = t2.sEmployeeId and t1.dTotalHrs = t2.dTotalHrs
and t1.iTmPunchTimeSummaryId < t2.iTmPunchTimeSummaryId

select * from tmPunchtimeSummary


iTmPunchTimeSummaryId sCalldate sEmployeeId dTotalHrs
--------------------- -------------------- -------------------- --------------------
2 20060610 1234 4.5000
3 20060610 2468 8.0000
4 20060610 1357 9.0000
6 20060610 2345 8.5000

No comments: