[dba-SQLServer] Sum of elapsed time blocks

Glad I could help.

Mark


> Date: Tue, 23 Sep 2008 16:30:29 -0500
> From: erbachs at gmail.com
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Sum of elapsed time blocks
>
> Mark,
>
> I kept working at the query you sent me and I looked up ideas in Joe
> Celko's book, "SQL for Smarties". As I said earlier today, the "raw"
> query took 11-17 seconds to run because the table it queries contains
> 1.4 million rows and the self-join has to run quite a few times and
> indexes have to be built, etc.
>
> So I built a couple temp tables first. The following routine assumes
> that there is a one-to-one match between "In" and "Out" records
> (except for the most recent "In" record). It executes lickety-split:
>
> SELECT
> EmployeeID,
> [TimeStamp],
> AreaCode
> INTO [_TempIn]
> FROM AccessEvents
> WHERE EmployeeID = 337 AND
> [TimeStamp]> CONVERT(datetime, '9/15/2008') AND
> AreaCode = 'In';
>
> SELECT
> EmployeeID,
> [TimeStamp],
> AreaCode
> INTO [_TempOut]
> FROM AccessEvents
> WHERE EmployeeID = 337 AND
> [TimeStamp]> CONVERT(datetime, '9/15/2008') AND
> AreaCode = 'Out';
>
> SELECT
> I.EmployeeID,
> I.[TimeStamp] AS TimeIn,
> (
> SELECT
> Min(O.[TimeStamp]) AS TimeOut
> FROM [_TempOut] AS O
> WHERE
> (
> (O.EmployeeID = I.EmployeeID) AND
> (O.[TimeStamp]> I.[TimeStamp])
> )
> ) AS TimeOut
> FROM [_TempIn] AS I
> ORDER BY I.[TimeStamp];
>
> DROP TABLE [SteveErbach].[_TempIn];
> DROP TABLE [SteveErbach].[_TempOut];
>
> Thanks for the original query and the idea.
>
> Steve Erbach
> Neenah, WI
>
> On Mon, Sep 22, 2008 at 12:58 PM, Mark A Matte  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"));
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>

_________________________________________________________________
Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie.
http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008