sql-server-2005基于日期连续3天查询第二次记录

Question

我需要为在连续3天内有事件(访问构建)的每个员工获取第二条记录，并且只为每个员工获取最古老的结果。

时间没有被考虑在内。

我的桌子是这样的：

EMPID   EVENTIME
4   2015-08-05 13:34:54.000
4   2015-08-19 16:29:32.000
4   2015-08-21 16:30:35.000
4   2015-08-24 13:51:25.000
4   2015-08-24 16:32:39.000
4   2015-08-26 13:48:32.000
4   2015-08-26 16:29:58.000
4   2015-08-27 16:30:07.000
4   2015-08-28 14:00:02.000
4   2015-08-28 16:29:09.000
19  2015-08-10 07:27:10.000
19  2015-08-10 15:18:51.000
19  2015-08-11 07:33:12.000
19  2015-08-11 07:33:16.000
19  2015-08-11 10:19:56.000
19  2015-08-11 15:49:12.000
19  2015-08-12 07:21:06.000
19  2015-08-12 10:37:53.000
19  2015-08-12 12:48:12.000
19  2015-08-12 14:11:25.000
19  2015-08-12 15:01:18.000
19  2015-08-13 07:25:38.000
21  2015-08-03 10:07:00.000
21  2015-08-10 08:00:41.000

预期的结果如下所示：

EMPID   EVENTIME
4   2015-08-27 //first record that had an event 1 day before and 1 day after
19  2015-08-11 //first record that had an event 1 day before and 1 day after

由于访问是由接近卡控制的，这些卡会被测试，几天后交付给新员工，那么第一张记录就无法工作，而连续3天的第一张记录也不起作用，因为卡片是在下午发送的，早上发生的失踪事件。这就是为什么我需要对前3天的第二次记录进行查询。

这将给我的每个员工的开始日期，我可以使用的报告。

我正在使用mssql 2005。

谢谢!

Answer 1

经过几个小时的挣扎，我终于做到了。如果有人需要它：

WITH RESULTS AS(
    SELECT EVENTS.EMPID, CAST(DATEDIFF(d,0,EVENTS.EVENTIME) AS DATETIME) AS EVENTIME, COUNT(CAST(DATEDIFF(d,0,EVENTS.EVENTIME) AS DATETIME)) AS THIS_DAY
    FROM EVENTS INNER JOIN EMP ON EVENTS.EMPID = EMP.ID
    WHERE (EVENTIME > '2015-08-01')
    GROUP BY EVENTS.EMPID, CAST(DATEDIFF(d,0,EVENTS.EVENTIME) AS DATETIME)
)
SELECT A.EMPID, MIN(A.EVENTIME) AS EVENTIME
FROM RESULTS A
    INNER JOIN RESULTS B ON A.EMPID = B.EMPID
    AND CAST(DATEDIFF(d,0,B.EVENTIME) AS DATETIME) = CAST(DATEDIFF(d,0,DATEADD(d,-1,A.EVENTIME)) AS DATETIME)
    INNER JOIN RESULTS C ON B.EMPID = C.EMPID
    AND CAST(DATEDIFF(d,0,C.EVENTIME) AS DATETIME) = CAST(DATEDIFF(d,0,DATEADD(d,1,A.EVENTIME)) AS DATETIME)
GROUP BY A.EMPID
ORDER BY A.EMPID