complicated sql query

Hello all,
I have a pretty complicated query (at least for me) I'd need some help
on.
Here's the thing.
I have an occupancy sensor that logs a timestamp in a log table every
10 seconds when a person is detected.
If no one is detected, no log is written.
Now I need to count the number of visit and the duration on that room
so I built that query:
SELECT TOP (100) PERCENT ActivityID, LocationName, Timestamp,
DATEDIFF(ss,
(SELECT MAX(Timestamp)
FROM dbo.tbl_ActivityLog AS
Previous
WHERE (LocationName =
[Current].LocationName) AND (Timestamp < [Current].Timestamp) AND
(DATEDIFF(ss,
Timestamp, [Current].Timestamp) <= 15)), Timestamp) AS Duration
FROM dbo.tbl_ActivityLog AS [Current]
That query works well, retrieving a row for every 10 seconds when
someone is in the room and with a Duration=NULL when the difference
between 2 detection is > 15 s to count that case as a different visit
(15 because sometimes is the sensor is too busy, it takes more than 10
seconds to notify)
Here's a simple example of data retrieved with that query:
ActivityID Location Timestamp Duration
1 loc1 8/13/2007 1:18:03 AM NULL
2 loc1 8/13/2007 1:18:13 AM 10
3 loc1 8/13/2007 1:18:23 AM 10
4 loc1 8/13/2007 3:34:50 AM NULL
When the duration is null, I know than between the last visit and the
current visit, more than 15 seconds passed so I have to count it as a
new visit.
Now my question:
I would like to have a query that will give me:
- the number of visits for a location,
- the duration for each visit.
Thanks for your help, and sorry to be so verbose
PatriceOn Tue, 14 Aug 2007 11:40:20 -0700, plevexier@.gmail.com wrote:

>Hello all,
>I have a pretty complicated query (at least for me) I'd need some help
>on.
>Here's the thing.
(snip)
>Now my question:
>I would like to have a query that will give me:
>- the number of visits for a location,
>- the duration for each visit.
>
>Thanks for your help, and sorry to be so verbose
Hi Patrice,
Could you please post the table structure (as a CREATE TABLE statement,
including all constraints, properties, and indexes - though you may omit
irrelevant columns), some well-chosen rows of sample data (as INSERT
statemtents), and expected results? See www.aspfaq.com/5006 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis