mercredi 4 novembre 2015

sql-server-2005 query 2nd record of 3 consecutive days based on date

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