Get minimum and maximum datetime for each row between a date range
<p>I'm struggling with this! My data is like the table below except there would be more than one user. Note that it isn't just a start/end time, there are many dates in between. </p>

<pre><code>+-------------------------+--------+---------------------------+
| Date                    | Name 2 | Access                    |
+-------------------------+--------+---------------------------+
| 2014-09-29 14:50:03.000 | User1  | Ground Floor Door 1 (In)  |
+-------------------------+--------+---------------------------+
| 2014-09-30 08:42:33.000 | User1  | Ground Floor Door 2 (In)  |
+-------------------------+--------+---------------------------+
| 2014-09-30 08:42:58.000 | User1  | 1st Floor Door 1 (In)     |
+-------------------------+--------+---------------------------+
| 2014-09-30 12:31:54.000 | User1  | Ground Floor Door 1 (Out) |
+-------------------------+--------+---------------------------+
| 2014-09-30 13:05:43.000 | User1  | Ground Floor Door 1 (In)  |
+-------------------------+--------+---------------------------+
| 2014-09-30 13:11:32.000 | User1  | Ground Floor Door 3 (Out) |
+-------------------------+--------+---------------------------+
| 2014-09-30 13:55:28.000 | User1  | Ground Floor Door 1 (In)  |
+-------------------------+--------+---------------------------+
| 2014-09-30 13:55:36.000 | User1  | Ground Floor Door 2 (In)  |
+-------------------------+--------+---------------------------+
| 2014-09-01 18:31:21.000 | User1  | Ground Floor Door 4 (Out) |
+-------------------------+--------+---------------------------+
| 2014-10-01 08:31:21.000 | User1  | Ground Floor Door 1 (In)  |
+-------------------------+--------+---------------------------+
| 2014-10-01 08:31:41.000 | User1  | Ground Floor Door 2 (In)  |
+-------------------------+--------+---------------------------+
| 2014-10-01 17:31:49.000 | User1  | Ground Floor Door 4 (Out) |
+-------------------------+--------+---------------------------+
| 2014-10-02 08:31:51.000 | User1  | Ground Floor Door 1 (In)  |
+-------------------------+--------+---------------------------+
| 2014-10-02 18:41:49.000 | User1  | Ground Floor Door 3 (Out) |
+-------------------------+--------+---------------------------+
</code></pre>

<p>I need to obtain the minimum and maximum value for each day between a range of dates. If I could calculate how long the lunch period was (difference between the first and last swipes between 12pm-2pm) that would be great as well but not important until I can get the first part working.</p>

<p>I've tried various versions of the query below so far but had no joy. Can anyone help me please? I have no problem getting the min and max for a specific date, it's only when there is a range involved that it doesn't work.</p>

<pre><code>select min(Date) as EntryTime, max(Date) as ExitTime
from table
where [Name 2] like '%User1%' and EventTime between '2014-09-30 12:00:00' and '2014-10-05 12:00:00'
group by cast(Date as datetime)
</code></pre>

<p>This doesn't give the desired result set. I want my query to return something like this for my table:</p>

<pre><code>+-------------------------+-------------------------+----------+------------+
| EntryTime               | ExitTime                | Username | Date       |
+-------------------------+-------------------------+----------+------------+
| 2014-09-30 08:42:33.000 | 2014-09-01 18:31:21.000 | User1    | 2014-09-01 |
+-------------------------+-------------------------+----------+------------+
| 2014-10-01 08:31:21.000 | 2014-10-01 17:31:49.000 | User1    | 2014-10-01 |
+-------------------------+-------------------------+----------+------------+
| 2014-10-02 08:31:51.000 | 2014-10-02 18:41:49.000 | User1    | 2014-10-02 |
+-------------------------+-------------------------+----------+------------+
</code></pre>