Take the nearest row by condition in date, student frame-CodePudding

I am struggling to get the nearest 'Math Test' or 'Biology Test' in ( /- 3 hours) from Test= 'Marked A ' including TestOrder ordering.

If 'Math Test' or 'Biology Test' were before 'Marked A ' with the same time - I get the max TestOrder to the 'Marked A '

If 'Math Test' or 'Biology Test' were after 'Marked A ' with the same time - I get the min TestOrder to the 'Marked A '

| student | Test         | TestOrder | DateTime                |
| ------- | ------------ | --------- | ----------------------- |
| 1       | Math Test    | 22        | 2022-05-01 19:06:16.207 |
| 1       | Biology Test | 32        | 2022-05-01 19:06:16.207 |
| 1       | Marked A     | 50        | 2022-05-01 19:06:16.407 |
| 1       | Math Test    | 22        | 2022-05-01 20:06:16.100 |
| 1       | Biology Test | 32        | 2022-05-01 20:06:16.100 |
| 2       | Math Test    | 22        | 2022-05-01 18:06:16.407 |
| 2       | Biology Test | 32        | 2022-05-01 18:06:16.407 |
| 2       | Marked A     | 50        | 2022-05-01 19:06:16.407 |
| 2       | Math Test    | 22        | 2022-05-01 19:07:16.407 |
| 2       | Biology Test | 32        | 2022-05-01 19:07:16.407 |
| 3       | Math Test    | 22        | 2022-05-01 10:36:12.207 |
| 3       | Biology Test | 32        | 2022-05-01 19:02:16.407 |
| 3       | Marked A     | 50        | 2022-05-01 19:06:16.407 |
| 3       | Math Test    | 22        | 2022-05-01 20:06:14.002 |
| 3       | Biology Test | 32        | 2022-05-01 21:06:10.107 |
| 4       | Math Test    | 22        | 2022-05-01 17:06:22.101 |
| 4       | Biology Test | 32        | 2022-05-01 18:06:22.101 |
| 4       | Marked A     | 50        | 2022-05-01 19:06:16.407 |
| 4       | Math Test    | 22        | 2022-05-01 19:06:20.407 |
| 4       | Biology Test | 32        | 2022-05-01 23:06:20.407 |

Final result has 'Marked A ' left joined to the nearest events 'Math Test' or 'Biology Test' across students.

| 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 |

SELECT t1.student, t1.Test, t1.TestOrder, t1.Datetime
, t2.student_, t2.Test_, t2.TestOrder_, t2.Datetime_
FROM tab1 t1
left join tab1 t2 
    ON t2.Test in ('Math Test', 'Biology Test')          
    AND t2.student = t1.student
    AND DATEADD(HOUR, -3, t1.DateTime) <= t2.DateTime and t2.DateTime < DATEADD(HOUR, 3, t1.DateTime) /* Take from the nearest 'Marked A ' ( /-3 hours) */
WHERE t1.Test = 'Marked A '

But I have no I idea how to take the nearest 'Math Test', 'Biology Test' according to TestOrder column

PS: I am using MSSQL

On the screenshot I highlighted must be taken rows

CodePudding user response：

The following uses apply to match the closest row by [Datetime] to the source row. It doesn't deal with duplicates however, for which you haven't clarified any requirements, but see if this works for you?

select *
from t
cross apply (
    select top(1) *
    from t t2
    where t.student = t2.student and t2.Test in ('Math Test', 'Biology Test') 
      and Abs(DateDiff(minute,t2.[datetime],t.[datetime]))<= 180
    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 ';

See Demo Fiddle