Deleting Partially Duplicate Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting Partially Duplicate Records

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-10-07 : 01:29:21

Hi :
V have a script which runs daily to insert all emp records from txt file into a table. Something went wrong and instead of having one record of each emp for a particular date the record got split into two. The Foll sample shows the splitting of EMIDNO 1 AND 3
for 2003-10-03

EMIDNO TRANDATE ATTEND REV
1 2003-10-01 PRESENT 4000
1 2003-10-02 PRESENT 4200
1 2003-10-03 PRESENT NULL
1 2003-10-03 NULL 5800

2 2003-10-01 PRESENT 4000
2 2003-10-02 PRESENT 4150
2 2003-10-03 PRESENT 4060

3 2003-10-01 PRESENT 4100
3 2003-10-02 PRESENT 1200
3 2003-10-03 PRESENT NULL
3 2003-10-03 NULL 1100

4 2003-10-01 PRESENT 6000
4 2003-10-02 PRESENT 6150
4 2003-10-03 PRESENT 7840

I read the article written by Graz about "Deleting Duplicate Records", but in my case the records are not exactly duplicated. So I want to merge the splitted records into one record. Something like this :

EMIDNO TRANDATE ATTEND REV
1 2003-10-01 PRESENT 4000
1 2003-10-02 PRESENT 4200
1 2003-10-03 PRESENT 5800

2 2003-10-01 PRESENT 4000
2 2003-10-02 PRESENT 4150
2 2003-10-03 PRESENT 4060

3 2003-10-01 PRESENT 4100
3 2003-10-02 PRESENT 1200
3 2003-10-03 PRESENT 1100

4 2003-10-01 PRESENT 6000
4 2003-10-02 PRESENT 6150
4 2003-10-03 PRESENT 7840

Any help will be really appreciated.

Thanks,
- Marjo.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-07 : 01:52:59

Take a backup of the table first and do this:

--Get all the merged records into a temporary table
SELECT EMIDNO, TRANDATE, MAX(ATTEND) AS ATTEND, MAX(REV) AS REV
INTO #MergedRecords
FROM TableName
GROUP BY EMIDNO, TRANDATE

-Clear existing data
DELETE FROM TableName

--Insert clean data back into table
INSERT INTO TableName
SELECT * FROM #MergedRecords

You might have to tweak it a little to suit your requirements, if you need more help, let us know.

Owais

Make it idiot proof and someone will make a better idiot

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-10-07 : 06:16:25

Thanks A LOT mohdowais, it really worked.
But there is one slight problem that I 4got mention in my prev post. Some employee records CAN be repeated but with different values in ATTEND field. For Eg :

EMIDNO TRANDATE ATTEND REV
2 2003-10-01 PRESENT 4000
2 2003-10-02 PRESENT 4150
2 2003-10-03 PRESENT 4060
2 2003-10-03 WORKED 2SHIFT 4060

So it merges these records also and returns

EMIDNO TRANDATE ATTEND REV
2 2003-10-01 PRESENT 4000
2 2003-10-02 PRESENT 4150
2 2003-10-03 WORKED 2SHIFT 4060

and I can't filter on date bcoz it could be any date.

Thanks
- Marjo.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-07 : 06:34:55

SELECT distinct
EMIDNO, TRANDATE, ATTEND, REV
INTO #MergedRecords
FROM TableName
WHERE NOT (ATTEND is null OR REV is null)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources