[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 <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"));
>