asked on

MS SQL Last Entry For Day

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

ASKER CERTIFIED SOLUTION

smilieface

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

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