[dba-SQLServer] Sum of elapsed time blocks

Steve,

I think this will get you to having your times in a single record...I'm sure you can do the math from here.  This does NOT test for missed punches/times...or whatever you are calling them.

Hope it helps...Mark A. Matte

SELECT MT.AgentID, MT.DTTM, MT.Direction, (SELECT Min(SM.DTTM) AS MinOfDTTM
FROM tblTimes AS SM
WHERE (((SM.AgentID)=MT.AgentID) AND ((SM.Direction)="out"))
and (((SM.DTTM)>MT.DTTM));) AS Expr1
FROM tblTimes AS MT
WHERE (((MT.Direction)="in"));


> From: stuart at lexacorp.com.pg
> To: dba-sqlserver at databaseadvisors.com
> Date: Tue, 23 Sep 2008 03:00:13 +1000
> Subject: Re: [dba-SQLServer] Sum of elapsed time blocks
>
> 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  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  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
>>> _______________________________________________
>>> 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
>>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>

_________________________________________________________________
Get more out of the Web. Learn 10 hidden secrets of Windows Live.
http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008