SQL Server 2012 – Creating Joined Rows from Multiple Tables on Date Field

join;sql serversql-server-2012

I need to be able to take Table A and find the records that match in Table B and Table C based on its ID and add them together. The trick is that I only want to add the results from Table B and Table C that have the same year and ID and make that as its own row. On top of that, I need to know the latest date of the ones that were added together. The end result I am looking for is how Table D looks. I'm fairly new to SQL and have been stuck on this for quite some time so any help, clarification, or guidance would be greatly appreciated! 

Table A (Buyers)
+----+--------+
| Id |  Name  |
+----+--------+
|  1 | Donald |
|  2 | Tom    |
+----+--------+

Table B (Down Payments)
+--------+------------+----------+
| Amount |    Date    | TableAId |
+--------+------------+----------+
|     10 | 10/9/2005  |        1 |
|     20 | 9/2/2005   |        1 |
|     30 | 12/10/1993 |        2 |
|     40 | 3/2/1995   |        2 |
|     50 | 7/15/2001  |        1 |
+--------+------------+----------+

Table C (Payments)
+--------+------------+----------+
| Amount |    Date    | TableAId |
+--------+------------+----------+
|     25 | 5/20/2005  |        1 |
|     70 | 12/20/1993 |        2 |
|     45 | 1/14/1999  |        1 |
|    120 | 4/5/2006   |        2 |
|     20 | 3/15/1995  |        2 |
+--------+------------+----------+

Table D (Results)
+----+--------+--------+------------+
| Id |  Name  | Amount |    Date    |
+----+--------+--------+------------+
|  1 | Donald |     55 | 10/9/2005  |
|  1 | Donald |     50 | 7/15/2001  |
|  1 | Donald |     45 | 1/14/1999  |
|  2 | Tom    |    100 | 12/20/1993 |
|  2 | Tom    |     60 | 3/15/1995  |
|  2 | Tom    |    120 | 4/5/2006   |
+----+--------+--------+------------+

Best Answer

Let's start by considering the downpayments and payments together.

WITH AllPayments AS (
    SELECT * FROM TableB
    UNION ALL
    SELECT * FROM TableC
)

Now get the total and latest date.

, YearlyDetails AS (
    SELECT TableAID, YEAR([Date]) AS TheYear,
        SUM(Amount), MAX([Date]) AS LastDate
    FROM AllPayments
    GROUP BY TableAID, YEAR([Date])
)

Now you can easily join this to TableA...

So putting all of it together:

WITH AllPayments AS (
    SELECT * FROM TableB
    UNION ALL
    SELECT * FROM TableC
), YearlyDetails AS (
    SELECT TableAID, YEAR([Date]) AS TheYear,
        SUM(Amount) AS Amount, MAX([Date]) AS LastDate
    FROM AllPayments
    GROUP BY TableAID, YEAR([Date])
)
SELECT a.ID, a.Name, y.Amount, y.LastDate as [Date]
FROM TableA AS a
JOIN YearlyDetails AS y
ON y.TableAID = a.ID;
Related Question