One to Many Join with Aggregate Function (Max)

Question

I have two tables.

Table 1:

+---------+---------+
| Lead_ID | Deal_ID |
+---------+---------+
|    2323 | null    |
|    2324 | 1199    |
|    2325 | null    |
|    2326 | null    |
|    2327 | 1080    |
+---------+---------+

Table 2:

+---------+-------------+-------------+------------+
| Deal_ID | Stage_From  |  Stage_To   | ChangeDate |
+---------+-------------+-------------+------------+
|    1199 | incoming    | stage1      | 01-Dec-14  |
|    1199 | stage1      | incoming    | 05-Dec-14  |
|    1199 | incoming    | stage1      | 12-Dec-14  |
|    1080 | incoming    | unqualified | 06-Dec-14  |
|    1080 | unqualified | stage2      | 07-Dec-14  |

I would like to add the "ChangeDate" for each Deal_ID that has a record in Table 2 where the Stage Changed from "Incoming" to "Stage1". In cases where the stage changed from "incoming" to "stage1" multiple times, I want to maximum date i.e. December 12, 2014 in the example rather than December 1, 2014.

Resulting Table should be:

+---------+---------+------------+
| Lead_ID | Deal_ID | ChangeDate |
+---------+---------+------------+
|    2323 | null    | null       |
|    2324 | 1199    | 12-Dec-14  |
|    2325 | null    | null       |
|    2326 | null    | null       |
|    2327 | 1080    | null       |
+---------+---------+------------+

I looked at a few similar questions which recommend using "Group_Concat", but I can't get to what I want with that method.

Any suggestions are greatly appreciated.

What if the fourth entry in table2 would be this ` 1080 | incoming | stage1 | 06-Dec-14 `? What's your expected result then? — 1000111, Apr 19 '16 at 03:24
@1000111 Then the ChangeDate in Resulting Table for 1090 should be 06-Dec-14. In ChangeDate I am looking for the latest date stage changed from incoming to stage1 — dsm309, Apr 19 '16 at 04:58

1000111 · Accepted Answer · 2016-04-19T05:27:45.100

Here's the query:

SELECT 
T.Lead_ID,
T.Deal_ID,
DATE_FORMAT(TT.maxChangeDate,'%d-%b-%y') AS ChangeDate
FROM Table1 T
LEFT JOIN 
(

   SELECT 
     Deal_ID,
     MAX(ChangeDate) maxChangeDate
     FROM Table2
   WHERE Stage_From = 'incoming'
   AND Stage_To = 'stage1'
   GROUP BY Deal_ID ) TT
ON TT.Deal_ID = T.Deal_ID;

SQL FIDDLE DEMO

Output:

Lead_ID Deal_ID ChangeDate
2323    (null)  (null)
2324    1199    12-Dec-14
2325    (null)  (null)
2326    (null)  (null)
2327    1080    (null)

Explanation:

Let's look at the following query (inside the TT block in the above query )

   SELECT 
     Deal_ID,
     MAX(ChangeDate) maxChangeDate
     FROM Table2
   WHERE Stage_From = 'incoming'
   AND Stage_To = 'stage1'
   GROUP BY Deal_ID;

This query gets the latest changeDate for each Deal_ID if they have incoming and stage1 as Stage_From and Stage_To respectively.

So this query returns the following result:

Deal_ID    maxChangeDate
1199        2014-12-12

Now if you make a LEFT JOIN between Table1 and this result then you will result for each in entry for Table1 having the corresponding maxChangeDateright after Lead_ID and Deal_ID.

One to Many Join with Aggregate Function (Max)

1 Answers1