1

I have been battling through this query/query design for sometime now and I thought it's time to ask the experts! Here's my table results:

ID   | Status       | date         |
---------------------------------
05   | Returned     | 20/6/2018    |
03   | Sent         | 12/5/2018    |
01   | Pending      | 07/6/2018    |
01   | Engaged      | 11/4/2018    |
03   | Contacted    | 16/4/2018    |
05   | Surveyed     | 04/3/2017    |
05   | No Contact   | 05/3/2017    |

How do I get it to return top/newest value for each ID:

ID   | Status       | date         |
---------------------------------
05   | Returned     | 20/6/2018    |
03   | Sent         | 12/5/2018    |
01   | Pending      | 07/6/2018    |

I've tried group by, TOP 1, Distinct and results still not what I wanted. Also, displaying the results by top 5% is won't do either as the ID can be more than just 3 types.

My QUERY below:

INSERT INTO TmpAllcomsEmployee ( StatusID, EmployeeID, CommunicationDate )
SELECT DISTINCT CommunicationLog.StatusID, TmpAllcomsEmployee.EmployeeID, 
Max(CommunicationLog.CommunicationDate) AS MaxOfCommunicationDate
FROM CommunicationLog RIGHT JOIN TmpAllcomsEmployee ON 
     CommunicationLog.EmployeeID = TmpAllcomsEmployee.EmployeeID
GROUP BY CommunicationLog.StatusID, TmpAllcomsEmployee.EmployeeID
ORDER BY Max(CommunicationLog.CommunicationDate) DESC;
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
aaendewy
  • 63
  • 4

1 Answers1

1

One method is a correlated subquery:

select cl.*
from CommunicationLog as cl
where cl.date = (select max(cl2.date)
                 from CommunicationLog as cl2
                 where cl2.EmployeeID = cl.EmployeeID
                );

This gets the most recent record for each employee in CommunicationLog. You can join in the other table if you really need it. It does not seem unnecessary unless you are using it for filtering.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786