sql - SQL Server - 如何获取根据名称和位置分组的每一天的最小和最大日期时间

Question

使用我们的门禁系统数据，我们被要求提供每天有人扫描每个建筑物的卡片的最早和最晚时间。该数据需要包括员工姓名。

基本上，每天先进后出每栋楼。

例如：

+-------------------------+--------------+------------+
|        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 |
+-------------------------+--------------+------------+

我们通过以下查询取得了一些成功：

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))

这个查询的问题是，如果数据中有任何相同的时间戳/扫描时间，它会返回两者。

无论如何我们可以提高这个查询的准确性吗？

标签： sqlsql-serverreporting

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);

或者查看您的查询，也许您要求：

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)

编辑：您的问题不清楚，阅读评论也许您是在问这个？：

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;