Gustav Brock
Gustav at cactus.dk
Mon Sep 22 11:54:36 CDT 2008
Hi Steve and Arthur I would keep the single rows but add a VisitId to the row: 11 337 2008-09-15 07:54:50.000 In 11 337 2008-09-15 11:59:56.000 Out 12 337 2008-09-15 12:38:24.000 In 12 337 2008-09-15 17:01:24.000 Out 13 337 2008-09-16 07:53:16.000 In 13 337 2008-09-16 11:58:00.000 Out 24 337 2008-09-16 12:33:35.000 In 24 337 2008-09-16 17:03:29.000 Out 25 337 2008-09-17 07:56:29.000 In 25 337 2008-09-17 11:59:14.000 Out 31 337 2008-09-17 12:49:41.000 In 31 337 2008-09-17 17:02:41.000 Out 77 337 2008-09-18 08:04:17.000 In If VisitStatus In/Out is a True/False, then all you need is a simple view to look up the row where VisitId match and VisitStatus1 = Not VisitStatus2 where you calculate the date/time diff. /gustav >>> fuller.artful at gmail.com 22-09-2008 18:29 >>> As Steve suggests, a cursor appears to be the way out of this, but I believe that this is because the model is wrong. In-to-out ought to be on one row not two. Then the problem erases itself. This approach might require some additional logic but in general, IMO, it is sound: Location1: In = 9:00am Location2: In = not permitted before Location1.Out is not null Location2: In >= Location1.Out My gut instinct tells me that it is a bad idea to store In and Out on separate rows. The concept ought to be rethought as a "Visit" which has an In time and an Out time.Then everything you need to know is on one single row and it's easy to grab and the duration is a cinch to calculate. Just my $0.02 Arthur On Mon, Sep 22, 2008 at 12:08 PM, Steve Erbach <erbachs at gmail.com> wrote: > Are we talking about using a SQL Cursor here? > > Steve Erbach > Neenah, WI > > On Mon, Sep 22, 2008 at 9:33 AM, Steve Erbach <erbachs at gmail.com> wrote: > > Dear Group, > > > > I wrote an upgrade to the classic ASP In/Out board my company uses in > > ASP.NET <http://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