By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table EmpRoleHistory(
HistoryId int,
EmpId int,
RoleName varchar(100),
TimeStamp datetime
)
insert into EmpRoleHistory
values
(1 , 1 , 'Developer' , '2020-02-06 8:00:00 AM'),
(2 , 1 , 'Lead' , '2020-02-06 8:00:00 PM'),
(3 , 1 , 'Mgr' , '2020-02-06 5:00:00 PM'),
(4 , 2 , 'Lead' , '2020-02-06 8:00:00 AM'),
(5 , 2 , 'Developer2' , '2020-02-06 5:00:00 PM'),
(6 , 2 , 'Mgr2' , '2020-02-06 8:00:00 PM'),
(7 , 3 , 'Mgr' , '2020-02-06 8:00:00 AM'),
(8 , 3 , 'Lead2' , '2020-02-06 5:00:00 PM'),
(9 , 3 , 'Developer3' , '2020-02-06 8:00:00 PM'),
(11, 3 , 'Developer4' , '2020-02-06 8:30:00 PM'),
(12, 1 , 'lead5' , '2020-02-06 8:15:00 PM')
11 rows affected
;with cte_TempTable as
(
Select EmpId, MAX(TimeStamp) as TimeStamp
From [dbo].[EmpRoleHistory]
Where TimeStamp <= '2020-02-06 17:00:00.000'
GROUP BY EmpId
)
select e.*
from EmpRoleHistory e
inner join cte_TempTable c on e.EmpId = c.EmpId
where e.TimeStamp = c.TimeStamp
ORDER BY e.EmpId
| HistoryId | EmpId | RoleName | TimeStamp |
|---|---|---|---|
| 3 | 1 | Mgr | 2020-02-06 17:00:00.000 |
| 5 | 2 | Developer2 | 2020-02-06 17:00:00.000 |
| 8 | 3 | Lead2 | 2020-02-06 17:00:00.000 |