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.
| Author |
Topic |
|
Gary Costigan
Yak Posting Veteran
95 Posts |
 Posted - 2005-07-13 : 12:05:36
|
| I have received a file that I need to load into a table that contains duplicate sets of data with the HOURS value in a "Plus and Minus" format. The reason for this is if an employee is originally code a paycode and then the paycode is changed we are now deducting the hours.Here is an example of the data.EMPID COSTCENTER PAYDATE PAYCODE HOURS001306 0435/4510 07/01/2001 UA 8.0001306 0435/4510 07/01/2001 UA -8.0When I load the data is there anyway of indentifying these two lines as duplicates and not load them?As always thanks in advance.GC |
|
|
tinkerman
Starting Member
13 Posts |
Posted - 2005-07-13 : 13:57:42
|
| SELECT EMPID, COSTCENTER, PAYDATE, PAYCODE, SUM(HOURS)FROM DataGROUP BY EMPID, COSTCENTER, PAYDATE, PAYCODE |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-13 : 15:32:57
|
| SELECT A.EMPID, A.COSTCENTER, A.PAYDATE, A.PAYCODE, A.HOURS FROM YourTable AS A JOIN (SELECT EMPID, COSTCENTER, PAYDATE, PAYCODE FROM YourTable GROUP BY EMPID, COSTCENTER, PAYDATE, PAYCODE HAVING COUNT(*) = 1) AS B ON A.EMPID = B.EMPID AND A.COSTCENTER = B.COSTCENTER AND A.PAYDATE = B.PAYDATE AND A.PAYCODE = B.PAYCODE |
|
|
|
|
|
|
|
|
Eliminating Duplicate Data