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 |
|
aaronc1982
Starting Member
2 Posts |
 Posted - 2008-01-31 : 14:33:22
|
| Hello,just wondering if anybody can help with this problem. I need to fix this data and I'm not sure how to achieve it.I have a table called TransactionHistory in MS SQL 2005. Among other variables I have the following:TransactionHistory.TransactionHistoryIDTransactionHistory.TransactionTypeIDTransactionHistory.ItemIDTransactionHistory.QuantityTransactionHistory.ReferenceSome sample records are as follows:Transaction HistoryID, TransactionTypeID, ItemID, Quantity, Reference1,12,123,100,##123456##2,13,123,100,##123456##3,12,456,500,##123456##4,13,456,500,##123456##5,13,456,500,##123546##This table has about 50k records in it. What I need to do is find a way to query the table to exclude the matching pairs. In this example matching pairs are 1 and 2, and 3 and 4. Record 5 is then the odd one out because its on its own. I want to delete the 'odd' entries from the table but I know how to do that. My definition of a matching pair is having one record with type of 12 and another of 13. They must have the same ItemID, Quantity and Reference. The reference may be included on more than one matching pair as well as 'odd' records. They may not be listed with sequential TransactionHistoryIDs and they almost certainly will have different dates/times. The variables above are the only ones that allow 2 entries in the table to have any common ground. Another way of thinking of it is that I only want to see the one-sided entries into this table. i.e those with only a transaction type 12 record or those with only a transaction type 13 record, but not both.There are about 47.5k entries with a TransactionTypeID of 12 and only about 2.5k entries with 13. I need to delete the other 45k but I need to find out exactly what they are first!Thanks for your input, it is gratefully received.CheersAaron |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-31 : 15:13:01
|
| You seem to be contradicting your requirements..you say "...I want to delete the 'odd' entries from the table " and mention historyid = 5 is odd because its on its own. The others exist in pairs of 12 and 13. But, later you say "I only want to see the one-sided entries into this table. i.e those with only a transaction type 12 record or those with only a transaction type 13 record, but not both."So which one is it.. do you want to delete those singleton rows or the pairs?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
aaronc1982
Starting Member
2 Posts |
Posted - 2008-02-01 : 01:47:54
|
| Sorry for the confusion!I want to delete the single entries. cheersAaron |
|
|
|
|
|
|
|
|
SQL Database problem