I need to fetch the second record for each employee that had events (access to building) in 3 consecutive days and only the oldest result for each employee.
Time is not taking into consideration.
My table look like this:
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
And the desired results would look like this:
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
Since access is controlled by proximity cards, and these are tested and then a few days later delivered to new employees, then the first record won't work, and the first record of these 3 consecutive days won't work either since cards are delivered in the afternoon missing events from the morning. That's why I need a query for the second record of the first 3 consecutive days.
This will give me a start date for each employee that I can use for reports.
I'm using mssql 2005.
Thank you!
Aucun commentaire:
Enregistrer un commentaire