MS SQL Last Entry For Day

Dave KIlby
Dave KIlby used Ask the Experts™
on
I would like to pull a report that shows me the last entry by an employee on a given day.

The fields are Date, Time, Employee, Status, Reason  - I would like to see the last entry for each employee for a given date which will be in the where statement

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
with cte_max as (
select 
   employeeId
   ,  [Date]
   ,  max([Time]) as MaxTime
   from [table]
   group by Employee, [Date]
   )
select 
   * 
   from cte_max
   inner join [table] t
      on cte_max.EmployeeId = t.EmployeeId
      and cte_max.[Date]    = t.[Date]
      and cte_max.MaxTime   = t.[Time]

Open in new window


Add your where clause to the CTE
Ryan ChongSoftware Team Lead

Commented:
another method is using Row_Number clause, such as

;with yourTable as
(
	Select '13 Jan 2020 13:13pm' yourDateTime, 'A' Employee, '1' Status, 'Bla bla..1' Reason union all
	Select '13 Jan 2020 13:14pm' yourDateTime, 'A' Employee, '1' Status, 'Bla bla..2' Reason union all
	Select '13 Jan 2020 13:15pm' yourDateTime, 'A' Employee, '1' Status, 'Bla bla..3' Reason union all
	Select '14 Jan 2020 13:13pm' yourDateTime, 'A' Employee, '1' Status, 'Bla bla..4' Reason union all
	Select '15 Jan 2020 13:13pm' yourDateTime, 'A' Employee, '1' Status, 'Bla bla..5' Reason union all
	Select '13 Jan 2020 13:13pm' yourDateTime, 'B' Employee, '1' Status, 'Bla bla..6' Reason union all
	Select '13 Jan 2020 13:13pm' yourDateTime, 'C' Employee, '1' Status, 'Bla bla..7' Reason union all
	Select '14 Jan 2020 13:13pm' yourDateTime, 'C' Employee, '1' Status, 'Bla bla..8' Reason union all
	Select '15 Jan 2020 13:13pm' yourDateTime, 'D' Employee, '1' Status, 'Bla bla..9' Reason
), cte as
(
	Select *, ROW_NUMBER() over (partition by Employee order by yourDateTime desc) idx
	from yourTable
)
Select * from cte
Where idx = 1
order by Employee

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial