Deleting Duplicate Records

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

code:

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?
Thanks,
Ninel
Message posted via http://www.webservertalk.comDELETE FROM tmPunchtimeSummary
WHERE EXISTS
(select * from tmPunchtimeSummary as X
where tmPunchtimeSummary.sCalldate = X.sCalldate
and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
and tmPunchtimeSummary.iTmPunchTimeSummaryId <
X.iTmPunchTimeSummaryId)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 16:41:01 GMT, "ngorbunov via webservertalk.com"
<
u9125@.uwe>
wrote:

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

code:

>
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?
>
>
Thanks,
>
Ninel|||Thank you very much.
Roy Harvey wrote:
>DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
> (select * from tmPunchtimeSummary as X
> where tmPunchtimeSummary.sCalldate = X.sCalldate
> and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
> and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
> and tmPunchtimeSummary.iTmPunchTimeSummaryId <
> X.iTmPunchTimeSummaryId)
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 32 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
In my figuring on paper, Roy's solution works.
I also offer another suggetion: Microsoft Access has lots and lots of
wizards to do hard stuff like that. So if you have a copy of Access, let it
be your quick-and-dirty friend. You can use the Access wizard to buzz up
some stuff really fast, and then just paste the SQL code into QA, clean it
up a bit, and then run it.
But I will warn you: Access is not up to the task of production code.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei