By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(student int, Test varchar(50), TestOrder int, [DateTime] datetime)
insert into t select 1 , 'Math Test', 22 ,'2022-05-01 19:06:16.207'
insert into t select 1 , 'Biology Test', 32 ,'2022-05-01 19:06:16.207'
insert into t select 1 , 'Marked A+', 50 ,'2022-05-01 19:06:16.407'
insert into t select 1 , 'Math Test', 22 ,'2022-05-01 20:06:16.100'
insert into t select 1 , 'Biology Test', 32 ,'2022-05-01 20:06:16.100'
insert into t select 2 , 'Math Test', 22 ,'2022-05-01 18:06:16.407'
insert into t select 2 , 'Biology Test', 32 ,'2022-05-01 18:06:16.407'
insert into t select 2 , 'Marked A+', 50 ,'2022-05-01 19:06:16.407'
insert into t select 2 , 'Math Test', 22 ,'2022-05-01 19:07:16.407'
insert into t select 2 , 'Biology Test', 32 ,'2022-05-01 19:07:16.407'
insert into t select 3 , 'Math Test', 22 ,'2022-05-01 10:36:12.207'
insert into t select 3 , 'Biology Test', 32 ,'2022-05-01 19:02:16.407'
insert into t select 3 , 'Marked A+', 50 ,'2022-05-01 19:06:16.407'
insert into t select 3 , 'Math Test', 22 ,'2022-05-01 20:06:14.002'
insert into t select 3 , 'Biology Test', 32 ,'2022-05-01 21:06:10.107'
insert into t select 4 , 'Math Test', 22 ,'2022-05-01 17:06:22.101'
insert into t select 4 , 'Biology Test', 32 ,'2022-05-01 18:06:22.101'
insert into t select 4 , 'Marked A+', 50 ,'2022-05-01 19:06:16.407'
insert into t select 4 , 'Math Test', 22 ,'2022-05-01 19:06:20.407'
insert into t select 4 , 'Biology Test', 32 ,'2022-05-01 23:06:20.407'
20 rows affected
select *
from t
cross apply (
select top(1) *
from t t2
where t.student = t2.student and t2.[datetime] <> t.[datetime]
order by Abs(DateDiff(minute,t2.[datetime],t.[datetime])) ,
case when t2.[datetime] > t.[datetime] then TestOrder end ,
case when t2.[datetime] < t.[datetime] then TestOrder end desc
)m
where t.test='Marked A+';
| student | Test | TestOrder | DateTime | student | Test | TestOrder | DateTime |
|---|---|---|---|---|---|---|---|
| 1 | Marked A+ | 50 | 2022-05-01 19:06:16.407 | 1 | Biology Test | 32 | 2022-05-01 19:06:16.207 |
| 2 | Marked A+ | 50 | 2022-05-01 19:06:16.407 | 2 | Math Test | 22 | 2022-05-01 19:07:16.407 |
| 3 | Marked A+ | 50 | 2022-05-01 19:06:16.407 | 3 | Biology Test | 32 | 2022-05-01 19:02:16.407 |
| 4 | Marked A+ | 50 | 2022-05-01 19:06:16.407 | 4 | Math Test | 22 | 2022-05-01 19:06:20.407 |