[dba-SQLServer] Sum of elapsed time blocks

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.  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