Stuart McLachlan
stuart at lexacorp.com.pg
Mon Sep 22 12:00:13 CDT 2008
Trouble is nost commercial time recording devices and systems log and store single "events" not visits. I think I've still got something like this stored away somewhere in Access ( it imported a text file which was dumped by just such a commercial system. I'll try to locate it. On 22 Sep 2008 at 13:29, Arthur Fuller wrote: > 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 > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >