SQL Pivot function to obtain top notes field

Question

Please see this question to gain more context: question 1 I have created a list of information using the pivot function using the following code below

  WITH List AS
    (
        SELECT 'x' AS Company, 'Call'    AS ContactType, '01/01/2016' AS StartTime UNION ALL
        SELECT 'x' AS Company, 'Call'    AS ContactType, '02/01/2016' AS StartTime UNION ALL
        SELECT 'x' AS Company, 'Meeting' AS ContactType, '02/01/2016' AS StartTime UNION ALL
        SELECT 'x' AS Company, 'Email'   AS ContactType, '03/01/2016' AS StartTime UNION ALL
        SELECT 'y' AS Company, 'Meeting' AS ContactType, '01/01/2016' AS StartTime UNION ALL
        SELECT 'y' AS Company, 'Email'   AS ContactType, '01/02/2016' AS StartTime UNION ALL
        SELECT 'y' AS Company, 'Call'    AS ContactType, '02/02/2016' AS StartTime UNION ALL
        SELECT 'z' AS Company, 'Call'    AS ContactType, '09/01/2016' AS StartTime UNION ALL
        SELECT 'z' AS Company, 'Call'    AS ContactType, '24/01/2016' AS StartTime UNION ALL
        SELECT 'z' AS Company, 'Meeting' AS ContactType, '10/01/2016' AS StartTime UNION ALL
        SELECT 'z' AS Company, 'Meeting' AS ContactType, '06/01/2016' AS StartTime 
    )


    SELECT Company, Call, Meeting, Email
    FROM
    (
      SELECT StartTime, ContactType, Company
        FROM List
    ) L
    PIVOT
    (
      MAX(StartTime)
      FOR ContactType IN (Call, Meeting, Email)
    ) CT

This helps produce a table outcome as so:

+---------+------------+------------+------------+
| Company |    Call    |  Meeting   |   Email    |
+---------+------------+------------+------------+
| x       | 02/01/2016 | 02/01/2016 | 03/01/2016 |
| y       | 02/02/2016 | 01/01/2016 | 01/02/2016 |
| z       | 24/01/2016 | 10/01/2016 | Null       |
+---------+------------+------------+------------+

I would like to take this further by incorporating an additional column into my original query. I'd like to have notes in the query like so:

WITH List AS
    (
        SELECT 'x' AS Company, 'Call'    AS ContactType, '01/01/2016' AS StartTime, 'blablabla1' as Note UNION ALL
        SELECT 'x' AS Company, 'Call'    AS ContactType, '02/01/2016' AS StartTime, 'blablabla2' as Note UNION ALL
        SELECT 'x' AS Company, 'Meeting' AS ContactType, '02/01/2016' AS StartTime, 'blablabla3' as Note UNION ALL
        SELECT 'x' AS Company, 'Email'   AS ContactType, '03/01/2016' AS StartTime, 'blablabla4' as Note UNION ALL
        SELECT 'y' AS Company, 'Meeting' AS ContactType, '01/01/2016' AS StartTime, 'blablabla5' as Note UNION ALL
        SELECT 'y' AS Company, 'Email'   AS ContactType, '01/02/2016' AS StartTime, 'blablabla6' as Note UNION ALL
        SELECT 'y' AS Company, 'Call'    AS ContactType, '02/02/2016' AS StartTime, 'blablabla7' as Note UNION ALL
        SELECT 'z' AS Company, 'Call'    AS ContactType, '09/01/2016' AS StartTime, 'blablabla8' as Note UNION ALL
        SELECT 'z' AS Company, 'Call'    AS ContactType, '24/01/2016' AS StartTime, 'blablabla9' as Note UNION ALL
        SELECT 'z' AS Company, 'Meeting' AS ContactType, '10/01/2016' AS StartTime, 'blablabla10' as Note UNION ALL
        SELECT 'z' AS Company, 'Meeting' AS ContactType, '06/01/2016' AS StartTime, 'blablabla11' as Note 
    )

The table output I would like to produce is:

+---------+------------+------------+------------+------------+
| Company |    Call    |  Meeting   |   Email    |    Note    |
+---------+------------+------------+------------+------------+
| x       | 02/01/2016 | 02/01/2016 | 03/01/2016 | blablabla4 |
| y       | 02/02/2016 | 01/01/2016 | 01/02/2016 | blablabla7 |
| z       | 24/01/2016 | 10/01/2016 | Null       | blablabla9 |
+---------+------------+------------+------------+------------+

Now the tricky bit. I would like the note that gets selected to be the most recent note based on the form of contact. In the top row note: blablabla4 gets selected because the most recent contact is via email '03/01/2016' therefore it selects this note.

Does anyone have any idea how i would achieve this without the use of sub querying my own data.

Answer 1

You can use ROW_NUMBER() to get the latest note

WITH List AS
    (
        SELECT 'x' AS Company, 'Call'    AS ContactType, '01/01/2016' AS StartTime, 'blablabla1' as Note UNION ALL
        SELECT 'x' AS Company, 'Call'    AS ContactType, '02/01/2016' AS StartTime, 'blablabla2' as Note UNION ALL
        SELECT 'x' AS Company, 'Meeting' AS ContactType, '02/01/2016' AS StartTime, 'blablabla3' as Note UNION ALL
        SELECT 'x' AS Company, 'Email'   AS ContactType, '03/01/2016' AS StartTime, 'blablabla4' as Note UNION ALL
        SELECT 'y' AS Company, 'Meeting' AS ContactType, '01/01/2016' AS StartTime, 'blablabla5' as Note UNION ALL
        SELECT 'y' AS Company, 'Email'   AS ContactType, '01/02/2016' AS StartTime, 'blablabla6' as Note UNION ALL
        SELECT 'y' AS Company, 'Call'    AS ContactType, '02/02/2016' AS StartTime, 'blablabla7' as Note UNION ALL
        SELECT 'z' AS Company, 'Call'    AS ContactType, '09/01/2016' AS StartTime, 'blablabla8' as Note UNION ALL
        SELECT 'z' AS Company, 'Call'    AS ContactType, '24/01/2016' AS StartTime, 'blablabla9' as Note UNION ALL
        SELECT 'z' AS Company, 'Meeting' AS ContactType, '10/01/2016' AS StartTime, 'blablabla10' as Note UNION ALL
        SELECT 'z' AS Company, 'Meeting' AS ContactType, '06/01/2016' AS StartTime, 'blablabla11' as Note 
    )
Select Company
      ,Call    = max(case when ContactType='Call'    then StartTime end)
      ,Meeting = max(case when ContactType='Meeting' then StartTime end)
      ,Email   = max(case when ContactType='Email'   then StartTime end)
      ,Note    = max(case when ContactType<>'Email' and RN=1 then Note end)
 From (
        Select *
              ,RN = Row_Number() over (Partition By Company,case when ContactType <>'EMail' then 1 else 0 end Order By StartTime Desc) 
         from List
      ) A
 Group BY Company

Returns

Company Call        Meeting     Email       Note
x       02/01/2016  02/01/2016  03/01/2016  blablabla3
y       02/02/2016  01/01/2016  01/02/2016  blablabla7
z       24/01/2016  10/01/2016  NULL        blablabla9