Select 2 rows based on datetime value
This query returns rows 1-2 and 6-7 because ActivityId 3 is before 2015-03-07 08:06:53.625 and ActivityId 4 is after 2015-03-07 08:06:53.625 for the 2 of them. 

Note: Since there is no unique ID column, I supposed that there is no duplicates in [StartTime] for a [UserName].

Query 1 (link):

DECLARE @date datetime = '2015-03-07 08:06:53.625';

WITH list AS (
    SELECT *
        , n = ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY [StartTime])
    FROM data
    WHERE [Activityid] IN (3, 4)
)
SELECT * 
FROM list l1
INNER JOIN list l2 ON l1.UserName = l2.UserName AND l1.n+1 = l2.n 
WHERE l1.Activityid = 3 AND l2.Activityid = 4
    AND l1.StartTime &lt; @date AND l2.StartTime &gt; @date;


Output 1:

Rownr   | UserName  | StartTime                     | Activityid    | Value | n | Rownr | UserName  | StartTime                     | Activityid    | Value | n
1       | Anneleen  | 2015-03-07 07:01:38.7070000   | 3             | 5811  | 1 | 2     | Anneleen  | 2015-03-07 08:52:23.4170000   | 4             | 5811  | 2
6       | Dimitri   | 2015-03-07 07:02:53.6330000   | 3             | 5817  | 1 | 7     | Dimitri   | 2015-03-07 21:24:20.6330000   | 4             | 5817  | 2




Using the same CTE this query returns rows where ActivityId 3 or 4 has no matching ActivityId 4 or 3.

Query 2 (link):

WITH list AS (
    SELECT *
        , n = ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY [StartTime])
    FROM data
    WHERE [Activityid] IN (3, 4)
)
SELECT l1.* , *
FROM list l1
LEFT JOIN list l2
    ON l1.UserName = l2.UserName AND l1.n+1 = l2.n AND l2.Activityid = 4
WHERE l1.Activityid = 3 AND l2.Activityid IS NULL
UNION ALL
SELECT l1.* , *
FROM list l1
LEFT JOIN list l2 ON l1.UserName = l2.UserName AND l1.n-1 = l2.n AND l2.Activityid = 3
WHERE l1.Activityid = 4 AND l2.Activityid IS NULL;


Output 2:

Rownr   | UserName  | StartTime                     | Activityid    | Value | n
5       | Arnold    | 2015-03-07 11:03:20.4200000   | 3             | 5840  | 1
8       | Dimitri   | 2015-03-07 22:24:20.6330000   | 4             | 5817  | 3


Data used with an extra row for 4 without 3:

declare @data table
    ([Rownr] int, [UserName] varchar(8), [StartTime] datetime2, [Activityid] int, [Value] int)
;

INSERT INTO @data
    ([Rownr], [UserName], [StartTime], [Activityid], [Value])
VALUES
    (1, 'Anneleen', '2015-03-07 07:01:38.707', 3, 5811),
    (2, 'Anneleen', '2015-03-07 08:52:23.417', 4, 5811),
    (3, 'Anneleen', '2015-03-07 08:52:34.347', 3, 5811),
    (4, 'Anneleen', '2015-03-07 14:15:20.140', 4, 5811),
    (5, 'Arnold', '2015-03-07 11:03:20.420', 3, 5840),
    (6, 'Dimitri', '2015-03-07 07:02:53.633', 3, 5817),
    (7, 'Dimitri', '2015-03-07 21:24:20.633', 4, 5817),
    (8, 'Dimitri', '2015-03-07 22:24:20.633', 4, 5817);