finding unmatched record/data in two sql tables
-
Hi you'll. I'm a newbie C# programmer. My project is to find unmatched records of two tables in sql database. The two tables are both transactions and i need to find the discrepancies. The columns include date,time,transaction ID and amount. I'm having difficulty because the tables are not identical, meaning both tables have extra column that I don;t need to match. What tools do I need to do this project. Please help.
if this is an SQL database i think you should use SQL... try adding a where clause to your query so it just returns the matching records...
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
-
if this is an SQL database i think you should use SQL... try adding a where clause to your query so it just returns the matching records...
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
Thanks for the reply guys! I have two sql tables, bank and store transactions. Unmatched data are datas/transactions that didn't match. example : table A A B C D E 100 10 11/11/2011 10:20:00 12 102 11 12/12/2012 10:23:00 12 103 12 12/13/2012 10:50:00 12 105 51 12/12/2012 10:22:00 12 106 55 12/15/2012 10:55:00 12 table B A B C D E 100 10 11/11/2011 13:20:00 12 102 11 12/12/2012 13:22:00 12 103 12 12/13/2012 13:50:00 12 105 51 12/12/2012 13:22:00 12 106 52 12/15/2012 13:55:00 12 I need to make a program that will show the unmatched record in column B. My project has about 300thou data's and also I need to adjust the time ' table A is in MT and Table B is in ET. I'm not good in explanation so if it's still too vague feel free to ask for more information... l.scott249@yahoo.com
-
Thanks for the reply guys! I have two sql tables, bank and store transactions. Unmatched data are datas/transactions that didn't match. example : table A A B C D E 100 10 11/11/2011 10:20:00 12 102 11 12/12/2012 10:23:00 12 103 12 12/13/2012 10:50:00 12 105 51 12/12/2012 10:22:00 12 106 55 12/15/2012 10:55:00 12 table B A B C D E 100 10 11/11/2011 13:20:00 12 102 11 12/12/2012 13:22:00 12 103 12 12/13/2012 13:50:00 12 105 51 12/12/2012 13:22:00 12 106 52 12/15/2012 13:55:00 12 I need to make a program that will show the unmatched record in column B. My project has about 300thou data's and also I need to adjust the time ' table A is in MT and Table B is in ET. I'm not good in explanation so if it's still too vague feel free to ask for more information... l.scott249@yahoo.com
In addition, if the records mismatched then show it. if they matched then ignore.
-
In addition, if the records mismatched then show it. if they matched then ignore.
(i'm from Brazil, so MT and ET aren't so familiar to me, but I've searched...) between ET and MT there are 2 hours of difference, so, you can add 2 hours to your time in ET to convert it to time in MT (if this is really necessary, please, verify) so, you can use the following select to get only the records that didn't match:
SELECT A.*, B.* --please, replace with the fields you really need...
FROM A
INNER JOIN B
ON A.A = B.A
WHERE (A.B <> B.B
OR A.C <> B.C
OR DATEADD(hour, 2, A.D) <> B.D --adding two hours to hour in ET converts it to hour in MT
OR A.E <> B.E) --replace with checks for the fields that you care aboutas you can see, it's very simple, but i'm making a lot of assumptions here, i'm assuming that your tables have a relation (foreign key constraint), that the field "A" on the two tables is the id of the transaction, that table B uses time in MT and that you care about any differences also, about times in different time zones, watch daylight saving time, this depends on the state that the time is saved, as a general warning, would be much better if your date and time were stored in UTC(so you don't need to care about time zones and DST) and in yyyy-MM-dd format. if this didn't solve your problem, please, let me know, so i can try to help you again.
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
-
(i'm from Brazil, so MT and ET aren't so familiar to me, but I've searched...) between ET and MT there are 2 hours of difference, so, you can add 2 hours to your time in ET to convert it to time in MT (if this is really necessary, please, verify) so, you can use the following select to get only the records that didn't match:
SELECT A.*, B.* --please, replace with the fields you really need...
FROM A
INNER JOIN B
ON A.A = B.A
WHERE (A.B <> B.B
OR A.C <> B.C
OR DATEADD(hour, 2, A.D) <> B.D --adding two hours to hour in ET converts it to hour in MT
OR A.E <> B.E) --replace with checks for the fields that you care aboutas you can see, it's very simple, but i'm making a lot of assumptions here, i'm assuming that your tables have a relation (foreign key constraint), that the field "A" on the two tables is the id of the transaction, that table B uses time in MT and that you care about any differences also, about times in different time zones, watch daylight saving time, this depends on the state that the time is saved, as a general warning, would be much better if your date and time were stored in UTC(so you don't need to care about time zones and DST) and in yyyy-MM-dd format. if this didn't solve your problem, please, let me know, so i can try to help you again.
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
Thanks a lot, Sorry about the time confusion. I'll try this and I'll let you know if it works.
-
(i'm from Brazil, so MT and ET aren't so familiar to me, but I've searched...) between ET and MT there are 2 hours of difference, so, you can add 2 hours to your time in ET to convert it to time in MT (if this is really necessary, please, verify) so, you can use the following select to get only the records that didn't match:
SELECT A.*, B.* --please, replace with the fields you really need...
FROM A
INNER JOIN B
ON A.A = B.A
WHERE (A.B <> B.B
OR A.C <> B.C
OR DATEADD(hour, 2, A.D) <> B.D --adding two hours to hour in ET converts it to hour in MT
OR A.E <> B.E) --replace with checks for the fields that you care aboutas you can see, it's very simple, but i'm making a lot of assumptions here, i'm assuming that your tables have a relation (foreign key constraint), that the field "A" on the two tables is the id of the transaction, that table B uses time in MT and that you care about any differences also, about times in different time zones, watch daylight saving time, this depends on the state that the time is saved, as a general warning, would be much better if your date and time were stored in UTC(so you don't need to care about time zones and DST) and in yyyy-MM-dd format. if this didn't solve your problem, please, let me know, so i can try to help you again.
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
Is it possible not to write a code in SQl and instead do it visual studio? Also, the unmatched data output needs to be in PDF file and also in charts. Any idea on this?
-
Is it possible not to write a code in SQl and instead do it visual studio? Also, the unmatched data output needs to be in PDF file and also in charts. Any idea on this?
yes, it's possible to write this code in C#, although, i can't think of a situation were you would like to bring all the data in the table to your application to do this, but if you really want, i suggest to take a read on the article that PIEBALDconsult indicated on this answer: http://www.codeproject.com/Messages/4250500/Re-finding-unmatched-record-data-in-two-sql-tables.aspx[^] also, about PDF and charts, i can't help you on this, but are various articles in this site about this, i've made a rapid search: http://www.codeproject.com/search.aspx?q=write+pdf+tag%3aC%23&doctypeid=1%3b2%3b3&sort=ratingdesc[^] i think this one is worthy a read: Gios PDF .NET library[^]
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
-
yes, it's possible to write this code in C#, although, i can't think of a situation were you would like to bring all the data in the table to your application to do this, but if you really want, i suggest to take a read on the article that PIEBALDconsult indicated on this answer: http://www.codeproject.com/Messages/4250500/Re-finding-unmatched-record-data-in-two-sql-tables.aspx[^] also, about PDF and charts, i can't help you on this, but are various articles in this site about this, i've made a rapid search: http://www.codeproject.com/search.aspx?q=write+pdf+tag%3aC%23&doctypeid=1%3b2%3b3&sort=ratingdesc[^] i think this one is worthy a read: Gios PDF .NET library[^]
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
Thank you so much!You Rock! if you were here in US,I'd be taking you to lunch lol!
-
(i'm from Brazil, so MT and ET aren't so familiar to me, but I've searched...) between ET and MT there are 2 hours of difference, so, you can add 2 hours to your time in ET to convert it to time in MT (if this is really necessary, please, verify) so, you can use the following select to get only the records that didn't match:
SELECT A.*, B.* --please, replace with the fields you really need...
FROM A
INNER JOIN B
ON A.A = B.A
WHERE (A.B <> B.B
OR A.C <> B.C
OR DATEADD(hour, 2, A.D) <> B.D --adding two hours to hour in ET converts it to hour in MT
OR A.E <> B.E) --replace with checks for the fields that you care aboutas you can see, it's very simple, but i'm making a lot of assumptions here, i'm assuming that your tables have a relation (foreign key constraint), that the field "A" on the two tables is the id of the transaction, that table B uses time in MT and that you care about any differences also, about times in different time zones, watch daylight saving time, this depends on the state that the time is saved, as a general warning, would be much better if your date and time were stored in UTC(so you don't need to care about time zones and DST) and in yyyy-MM-dd format. if this didn't solve your problem, please, let me know, so i can try to help you again.
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)
-
I don't think that would work. Surely an outer join is necessary? Something like this:
SELECT A.*, B.* --please, replace with the fields you really need...
FROM A
FULL OUTER JOIN B
ON A.A = B.A AND WHERE A.A IS NULL OR B.A IS NULLthe inner join is to take the correspondingly registries, that have the same key, in the "where" is where the actual matching is performed, verifing what fields of the table doesn't match. the id must always match, else, how do you know what transaction corresponds to what? in your query you take the registries that don't have pair in the other table, what the op seems do seek is the registries that have pair, but with different values.
I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)