У
SELECT
a.n,
min(a.t) min_before,
CASE
WHEN a.v = 'BRUN' THEN a.jt
WHEN a.v = 'BLOND' THEN Max(CASE WHEN a.t < a.jt THEN a.t END)
END max_before,
a.jt min_after,
max(a.t) max_after
FROM
(
SELECT
max(CASE WHEN t.c = 'JOPA' THEN t.t END) OVER (PARTITION BY t.n
ORDER BY
t.t) jt, t.*
FROM
TABLE t) a
GROUP BY
a.n, a.jt
a.n,
min(a.t) min_before,
CASE
WHEN a.v = 'BRUN' THEN a.jt
WHEN a.v = 'BLOND' THEN Max(CASE WHEN a.t < a.jt THEN a.t END)
END max_before,
a.jt min_after,
max(a.t) max_after
FROM
(
SELECT
max(CASE WHEN t.c = 'JOPA' THEN t.t END) OVER (PARTITION BY t.n
ORDER BY
t.t) jt, t.*
FROM
TABLE t) a
GROUP BY
a.n, a.jt