| Author |
 Topic  |
|
|
martii
Starting Member
United Kingdom
3 Posts |
 Posted - 11/30/2012 : 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. |
Edited by - martii on 11/30/2012 05:05:36
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/30/2012 : 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
United Kingdom
3 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/30/2012 : 05:47:20
|
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 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/30/2012 : 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
United Kingdom
3 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/30/2012 : 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. |
|
|
| |
Topic |
|
difficult group by query
