DECLARE @tab1 TABLE([time] time, [user] varchar(10))
INSERT INTO @tab1
SELECT '08:30', null union all 
SELECT '09:00',  null union all 
SELECT '09:30', 'Martii' union all 
SELECT '10:00', 'Martii' union all 
SELECT '10:30', 'Martii' union all 
SELECT '11:00',  null union all 
SELECT '11:30',  null
/*
[start] [end] [count] [user]
08:30 09:00 2 null
09:30 10:30 3 martii
11:00 11:30 2 null
*/
SELECT MIN(t.[time]) AS [start], MAX(t.[time]) AS [end], COUNT([user] * ) [count], t.[user] 
FROM @tab1 t
OUTER APPLY (SELECT MIN([time]) AS NextValue
				FROM @tab1
				WHERE [time] > t.[time]
				AND ( COALESCE([USER], '') <> COALESCE(t.[user], '') )
			) t1
GROUP BY t.[user], NextValue
ORDER BY [start]

quote:

---

Originally posted by nigelrivett

Needs a count(*) to cater for null user

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by nigelrivett

But I like the solution - I always forget about these new fangled instructions :).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by martii

WOW you make it look so easy, i will now spend the next hour working out exactly what's going on. I'd rather understand it than simply just copy and use it.

Thank you both very much, beer and pasties waiting if you ever find yourselves in Cornwall, England

Martii

==============================================
on my tombstone please put :- "so far so good"

---