I've got a data set where I have Employee ID's with varying Assignment ID's that have monthly pay amounts. I'd like to determine which of the Employee's Assignment ID's had the most Pay Amount per month/year. I've attached an excel example of the data set along with what my desired data set output would be.
From my Data Set I basically want to Determine the Max Amount Value Group By [EmployeeID], [Month], [Year] and then Return whichever AssignmentID is corresponding to that Max Value amount
Something like this:
SELECT Transactions.[EmployeeID], Transactions.[AssignmentID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year];