Steve Erbach
erbachs at gmail.com
Mon Sep 22 09:33:37 CDT 2008
Dear Group, I wrote an upgrade to the classic ASP In/Out board my company uses in ASP.NET. The security cards we all have allow us access to the building and the In/Out times are recorded in a SQL table. I wonder if you could help me visualize a query that would total up the elapsed time IN for an employee over a defined period of time, say, from Monday at 12:01 AM to now. The table, AccessEvents, contains these main fields: EmployeeID TimeStamp AreaCode The AreaCode contains either the area of the building that the employee entered upon coming in, or the "outside", meaning that the employee is out of the building. Say that it's Thursday and the employee's AccessEvents records look like the following (I've changed the AreaCode to In or Out for simplicity): 337 2008-09-15 07:54:50.000 In 337 2008-09-15 11:59:56.000 Out 337 2008-09-15 12:38:24.000 In 337 2008-09-15 17:01:24.000 Out 337 2008-09-16 07:53:16.000 In 337 2008-09-16 11:58:00.000 Out 337 2008-09-16 12:33:35.000 In 337 2008-09-16 17:03:29.000 Out 337 2008-09-17 07:56:29.000 In 337 2008-09-17 11:59:14.000 Out 337 2008-09-17 12:49:41.000 In 337 2008-09-17 17:02:41.000 Out 337 2008-09-18 08:04:17.000 In ...and the employee wants to know how much time he's spent in the building up to now, which is, say, 10:00AM on Thursday the 18th of Sept. It's easy enough to select these records, but I'm having trouble visualizing how to sum the elapsed time spent IN the building, including the time from his last In event to 10:00. Any ideas? Regards, Steve Erbach Neenah, WI