0

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   |
+----+--------+--------+------------+
2
  • I've only traced through Donald's values. It appears that someone can have a Payment without a DownPayment. Is the reverse possible: A DownPayment without a Payment record? – billinkc Feb 13 '15 at 23:00
  • Yes, the two values are separate and don't rely on each other – Toast Feb 16 '15 at 14:39
2

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;
0
0

This might be the ugliest thing I've written in a long time, but it returns your results.

    SELECT Amount.Id, Amount.Name, Amount.Amount, MaxOrder.MaxDate 
    FROM (
        SELECT a.Id, a.Name, SUM(Amount) AS Amount, a.[Year]
        FROM (
            SELECT Id, Name, SUM(Amount) AS Amount, YEAR(Date) as [Year]
            FROM TableA as a
            JOIN TableB as b on a.ID = b.TableAID
            GROUP BY Id, Name, YEAR(Date)
        UNION ALL
            SELECT Id, Name, SUM(Amount), YEAR(Date)
            FROM TableA as a
            JOIN TableC as C on a.ID = C.TableAID
            GROUP BY Id, Name, YEAR(Date)
            ) as a
        GROUP BY a.Id, a.Name, a.[Year]
        ) as Amount
    JOIN 
        (
            SELECT Id, [Year], MAX(Date) as MaxDate
            FROM (
                SELECT Id, YEAR(Date) AS [Year], Date
                FROM TableA as a
                JOIN TableB as b on a.ID = b.TableAID
                UNION ALL
                SELECT Id, YEAR(Date) AS [Year], Date
                FROM TableA as a
                JOIN TableC as C on a.ID = C.TableAID
                ) a
    GROUP BY a.Id, a.[Year]
        ) as MaxOrder on Amount.Id = MaxOrder.Id AND Amount.[Year] = YEAR(MaxOrder.MaxDate)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.