[dba-SQLServer] Sum of elapsed time blocks

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





More information about the dba-SQLServer mailing list