difficult group by query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

difficult group by query

Author

Topic

martii
Starting Member

3 Posts

Posted - 2012-11-30 : 04:45:19

any ideas on a 'group by' to get from :-
[time] [user]
08:30 Null
09:00 Null
09:30 Martii
10:00 Martii
10:30 Martii
11:00 Null
11:30 Null

to :-
[start] [end] [count] [user]
08:30 09:00 2 null
09:30 10:30 3 martii
11:00 11:30 2 null

I have tried ranks, row-numbers, and still cannot figure it out.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 05:30:24

select [start] = min([time]), [end] = max([time]), [count] = count(*), [user]
from tbl
group by [user]
order by [start]

==========================================
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.

martii
Starting Member

3 Posts

Posted - 2012-11-30 : 05:40:01

many thanks, i have just tried your code but got :-

start end count user
08:30 11:30 4 NULL
09:30 10:30 3 martii

it is counting all of the nulls in a single record

i need 3 records in total

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-30 : 05:47:20

[code]
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]
[/code]

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 05:57:19

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-30 : 05:59:12

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.

Yes you are right nigelrivett...

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 06:13:05

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.

martii
Starting Member

3 Posts

Posted - 2012-11-30 : 06:16:56

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"

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-30 : 06:20:12

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.

Oh Once upon a time i struggled with this type of queries. Thats why i remembered

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-30 : 06:21:01

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"

welcome...........

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 06:54:11

And here it is without the outer apply - but it is the same query really. Suspect the outer apply would be faster - be nice to check

;with cte as
(select *, t2 = (select min([time]) from @tab1 t2 where t2.[time] > t1.[time] and coalesce(t1.[user],'') <> coalesce(t2.[user],'')) from @tab1 t1)
select [start]=min([time]), [end] = max([time]), [count] = count(*), [user] = max([user])
from cte
group by t2
order by [start]

I checked the plan on my machine and the outer apply has a compute scalar inside the nestd loop whereas the derived table has it after which is odd.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources