Select 2 rows based on datetime value I have a database which is running on  SQL Server 2014.

Activityid = 3 means: log in

Activityid = 4 means: log out

Rownr is not a column (it indicates the number of the row)

Scenario 1)
The database contains a table with the following rows. I would like to select let say row 6 and 7 if the given datetime is 2015-03-07 08:06:53.625. Because the given datetime falls between the StartTime value of row 6 and 7. How can I achieve this?

Scenario 2)
The Activityid 4 is missing at row 5. How can I select rows with missing Activityid 3 or 4?

Rownr   UserName    StartTime               Activityid  Value
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


Rownr is not a column of the table. I've just added it to the result so I can point out which rows I want as result.
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);