Skip to content
Advertisement

How to get the latest date for each user id in a table

This is what I have so far, and it is pulling every single check for that Id. I need the latest checkdt for each Id. What am I missing?

SELECT e.Id
    ,e.EmployeeNumber
    ,e.LastName
    ,e.FirstName
    ,ec.CheckNumber
    ,ec.VoidDT
    ,MAX(ec.CheckDT) AS FirstChecktDT
FROM PR_Employee e
JOIN PR_Employeecheck ec ON ec.EmployeeId = e.id
WHERE e.employeestatusid IN (1,4)
    AND CheckNumber IS NOT NULL
GROUP BY e.Id
    ,e.EmployeeNumber
    ,e.LastName
    ,e.FirstName
    ,ec.CheckNumber
    ,ec.CheckDT
    ,ec.VoidDT
ORDER BY employeenumber
    ,CheckDT DESC

Advertisement

Answer

The problem is that in the GROUP BY clause you use columns from the table PR_Employeecheck so you are not grouping by employee but by employee and each matching row of the join.
Instead you can join the table PR_Employee to a query that uses ROW_NUMBER() window function to return the last date and remove the GROUP BY clause:

SELECT e.Id
    ,e.EmployeeNumber
    ,e.LastName
    ,e.FirstName
    ,ec.CheckNumber
    ,ec.VoidDT
    ,ec.CheckDT AS FirstChecktDT
FROM PR_Employee e
JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY CheckDT DESC) rn
  FROM PR_Employeecheck
) ec ON ec.EmployeeId = e.id
WHERE e.employeestatusid IN (1,4)
  AND CheckNumber IS NOT NULL
  AND ec.rn = 1
ORDER BY e.employeenumber
    ,ec.CheckDT DESC
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement