I am trying to write a union query that joins three tables that have the same exact format. Each table has 3 columns - invoice number, payment amount and payment date. Each of the tables has data from a different month (August, September and October). What I would like the union query to do is to provide me with a table listing all of the unique invoices from the 3 tables and the total amount paid on each and the latest payment date. It is possible for an invoice to be partially paid in Auugst and the rest paid in September. So if invoice 123 had $100 paid on August 7 and $150 paid on September 12, I would like the table to show me invoice 123 with $250 paid and September 12 as the payment date.
I think my problem is that I do not know how to sum in a union query or to show the max date in SQL.
Any help would be greatly appreciated. Thanks.
I think my problem is that I do not know how to sum in a union query or to show the max date in SQL.
Any help would be greatly appreciated. Thanks.