SQL Server - How to get the minimum and maximum datetime for each day grouped against name and location

Question

Using our door access system data, we have been asked to provide the earliest and latest time each day that someone scans a card for each building. This data needs to include the staff name.

Basically, the first in and the last out of each building every day.

For example:

+-------------------------+--------------+------------+
|        Scantime         |    Staff     |  Building  |
+-------------------------+--------------+------------+
| 2018-06-01 05:13:27.000 | Joe Bloggs   | Building 1 |
| 2018-06-01 05:30:19.000 | Mary Sue     | Building 2 |
| 2018-06-01 05:42:44.000 | Pete Generic | Building 3 |
| 2018-06-01 05:47:46.000 | Pete Generic | Building 4 |
| 2018-06-01 16:30:35.000 | Joe Bloggs   | Building 1 |
| 2018-06-01 16:46:49.000 | John Generic | Building 2 |
| 2018-06-01 16:58:11.000 | Joe Bloggs   | Building 4 |
| 2018-06-01 17:14:15.000 | Joe Bloggs   | Building 3 |
+-------------------------+--------------+------------+

We've had some success with the following query:

SELECT       ScanTime, Staff, Building
FROM            dbo.SentryJuno$ AS t1
WHERE        (ScanTime IN
                             (SELECT        MIN(ScanTime) AS Expr1
                               FROM            dbo.SentryJuno$
                               GROUP BY CONVERT(date, TimeStamp), Building)) OR
                         (ScanTime IN
                             (SELECT        MAX(ScanTime) AS Expr1
                               FROM            dbo.SentryJuno$ AS SentryJuno$_1
                               GROUP BY CONVERT(date, ScanTime), Building))

The issue with this query being that if there are any identical timestamps/scantimes in the data, it returns both.

Is there anyway we could improve the accuracy of this query?

Answer 1

SELECT Staff, Building, 
CAST(ScanTime AS DATE) AS ScanDate, 
MIN(ScanTime) AS FirstScanTime,
MAX(ScanTime) AS LastScanTime
FROM dbo.[SentryJuno$]
GROUP BY Staff, Building, CAST(ScanTime AS DATE);

Or looking into your query maybe you are asking for:

SELECT Staff, Building, 
CAST(ScanTime AS DATE) AS ScanDate, 
MIN(ScanTime) AS ScanTime
FROM dbo.[SentryJuno$]
GROUP BY Staff, Building, CAST(ScanTime AS DATE)
UNION 
SELECT Staff, Building, 
CAST(ScanTime AS DATE) AS ScanDate, 
MAX(ScanTime) AS ScanTime
FROM dbo.[SentryJuno$]
GROUP BY Staff, Building, CAST(ScanTime AS DATE)

EDIT: Your question is not clear, reading comments maybe you are asking for this?:

SELECT *
FROM dbo.SentryJuno$ s
     INNER JOIN(
               SELECT Building, CAST(Scantime AS DATE) AS ScanDate, MIN(Scantime) AS ScanTime
               FROM dbo.SentryJuno$
               GROUP BY Building, CAST(Scantime AS DATE)
               UNION
               SELECT Building, CAST(Scantime AS DATE) AS ScanDate, MAX(Scantime) AS ScanTime
               FROM dbo.SentryJuno$
               GROUP BY Building, CAST(Scantime AS DATE)
               ) fl ON fl.Building=s.Building
                  AND fl.ScanTime=s.Scantime;