I have a list that looks
| AccountID | Amount | Start_Date | End_Date | Invoice_Name |
|---|---|---|---|---|
| 1 | 250 | 05-05-2005 | 06-10-2016 | I23485 |
| 1 | 75 | 05-05-2005 | 06-10-2016 | I58487 |
| 1 | 75 | 03-05-2018 | 02-15-2022 | I77845 |
| 2 | 600 | 03-01-2003 | 08-10-2012 | I84897 |
| 2 | 400 | 03-01-2003 | 08-10-2012 | I76845 |
Ultimately, I am trying to group each Account_Number and sum the total and group it under the most recent End_Date and highest alphabetical order Invoice_Name.
| AccountID | Amount | Start_Date | End_Date | Invoice_Name |
|---|---|---|---|---|
| 1 | 400 | 03-05-2018 | 02-15-2022 | I77845 |
| 2 | 1000 | 03-01-2003 | 08-10-2012 | I84897 |
I have tried this, but it doesn't work:
SELECT
t1.AccountID, sum(t2.amount), t1.start_date, t1.end_date, t1.invoice_name
FROM Table1 t1
JOIN Table2 t2 on t1.AccountID = t2.ID
INNER JOIN (
Select AccountID, MAX(End_DATE) As MaxDate
FROM Table1
GROUP BY AccountID ) g
ON t1.AccountID = g.AccountID AND t1.end_date = g.MaxDate
GROUP BY t1.Accont,ID, t1.start_date, t1.end_date
ORDER BY sum(t2.amount) DESC
That works for a lot of the data, but when there are two Invoices for the same MaxDate, I am getting both with a duplicate amount. I only want the total amount and have it labeled under the most recent date and most recent invoice.
