SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 difficult group by query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

martii
Starting Member

United Kingdom
3 Posts

Posted - 11/30/2012 :  04:45:19  Show Profile  Reply with Quote
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.

Edited by - martii on 11/30/2012 05:05:36

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/30/2012 :  05:30:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

martii
Starting Member

United Kingdom
3 Posts

Posted - 11/30/2012 :  05:40:01  Show Profile  Reply with Quote
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"
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/30/2012 :  05:47:20  Show Profile  Reply with Quote

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]


--
Chandu

Edited by - bandi on 11/30/2012 06:00:11
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/30/2012 :  05:57:19  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/30/2012 :  05:59:12  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/30/2012 :  06:13:05  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

martii
Starting Member

United Kingdom
3 Posts

Posted - 11/30/2012 :  06:16:56  Show Profile  Reply with Quote
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"
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/30/2012 :  06:20:12  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/30/2012 :  06:21:01  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/30/2012 :  06:54:11  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000