erbachs at gmail.com
Tue Sep 23 11:46:14 CDT 2008
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.
More information about the dba-SQLServer mailing list