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:
Post a Comment