[dba-SQLServer] Sum of elapsed time blocks

Arthur,

» 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. «

This makes sense.  Unfortunately, the SQL database is part of the
security card access system.  I suppose that I could suggest to the
manufacturer that its design could be tweaked; but, even if they have
an epiphany, the design isn't likely to change soon.

So I'm left with my original question.  The more I look at it the more
I think I'll just bypass the all-in-one query idea and just feed the
result set to a .NET routine to step through the records.

Thanks, Arthur.

Steve Erbach
Neenah, WI

On Mon, Sep 22, 2008 at 11:29 AM, Arthur Fuller <fuller.artful at gmail.com> 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
>