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 |
|
Dayvson
Starting Member
14 Posts |
 Posted - 2003-10-23 : 05:57:50
|
hello people.. hi for all.. how long time I don't came here.. Well... I got a little problem that I'd like know how to solve it by sql ansi...Let's see it:We have an application here at my company. that it's made at delphi with interbase (gdb).Well.. that's ok, but we'll see it right now.This application consists to count pontuation and bonus for employees...Every month depending of affairs fromthe workers, they gain some pontuation and a little more at their's wage.Due to some problems some months ago , there was a problem...What happened?We're still evaluating this who's guilty... But it's no matter right now...The consequences is that there's duplication and triplication of bonus and pontuation for July month.We don't know exactly what may impact this... but we have solve it...We're thinking many ways solve it...But at the principle we like know how many matches with distinct situation.For example: How many workers is duplicated ? how many workers is triplicated? We have to know it.. cos we have to make a decision, cos some situations bring this:id history pointswork1 jul... 100work1 jul... 200If you realize it.. you'll see that there's 2 pontuations, but distinct values. Until now we don't knowwhat will be the critery to eliminate this.. But we're interested to know the quantity of duplications andtriplications.The structure from the tables are:Table Transactionsid seq creds debs historyWell.. I hope get some help from all of you... thank ya.. see ya people.. Dayvson Clauber  |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-10-23 : 07:39:44
|
select id, history, count(*) from table1group by id, historyhaving count(*) > 1will return duplicate, triplicates, etc...there are other topics here which will point you in the direction of solving the next question..."how do i remove/delete the duplicates"...a quick search will get you moving!also .... as a preventative measure ... to stop the problem getting worse/ reappearing....a 'unique' index(constraint) on id,history would be very useful.(by the way.....what is 'pontuation'?...does it have a cash element...or can it be drunk?) |
 |
|
|
Dayvson
Starting Member
14 Posts |
Posted - 2003-10-24 : 06:10:26
|
Exactly how u said andrew, Any way.. I should make this question before.. lol.. Cos now I am lost about this.. Then, if U told me that,, I guess you know where is it.. Then, please help me..I have to eliminate the "clones"id cred historic001 200 Bonus Jul/03 001 200 Bonus Jul/03 002 100 Bonus Jun/03 002 100 Bonus Jun/03 002 200 Bonus Jul/03 002 200 Bonus Jul/03 003 300 Bonus Jun/03 003 300 Bonus Out/02 003 300 Bonus Out/02But, look that there's diffs months....How may I get this? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-10-24 : 10:33:37
|
| read the enclosed....hopefully the advice contained therein will get you moving in the right direction.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256&SearchTerms=delete+duplicateone other alternative...is to unload the data to a flat file/other table...clear all rows, apply a unique index to the relevant columns...and then reload the data (ignoring the errors raised by the duplicates...after all you don't care which one of the duplicates is deleted....) |
|
|
|
|
|
|
|
|
problem with Interbase