[dba-SQLServer] Sum of elapsed time blocks

Mark,

Your query does, indeed, work.  I think, however, that I'm going to go
with the .NET loop-through-the-records idea because of query
performance.  I restricted the query results to the In/Out records for
one guy starting with Monday, Sep. 15th.  The query produced the
correct result but it takes 11 to 17 seconds (!!??) to fetch the
records from the SQL Server.

It's cool that SQL Server can do this much with one query, but man!
There are already 1.4 million records in the In/Out table and building
indexes is a bit of a chore.  I've suggested that some additional
clustered indexes be built for that table, but not action so far.

Thank you for posting that query.

Steve Erbach
Neenah, WI


On Mon, Sep 22, 2008 at 12:58 PM, Mark A Matte <markamatte at hotmail.com> wrote:
>
> 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.