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)
 Repetition of groups

Author  Topic 

Calkins
Starting Member

5 Posts

Posted - 2012-12-13 : 08:16:09
Hi.

I have problem with grouping rows in that way:

I have table of structure:

Value_Time State_ID
2012-10-22 06:16:37.000 101
2012-10-22 06:17:13.000 104
2012-10-22 06:17:27.000 104
2012-10-22 06:17:30.000 104
2012-10-22 06:17:46.000 104
2012-10-22 06:17:53.000 104
2012-10-22 06:18:00.000 107
2012-10-22 06:18:07.000 107
2012-10-22 06:18:12.000 107
2012-10-22 06:18:18.000 121
2012-10-22 06:18:21.000 121
2012-10-22 06:18:30.000 121
2012-10-22 06:18:35.000 121
2012-10-22 06:19:14.000 121
2012-10-22 06:19:19.000 121
2012-10-22 06:19:26.000 121
2012-10-22 06:19:33.000 6
2012-10-22 06:19:35.000 107
2012-10-22 06:19:42.000 107
2012-10-22 06:19:47.000 104
2012-10-22 06:19:54.000 104
2012-10-22 06:20:01.000 104
2012-10-22 06:20:37.000 104
2012-10-22 06:20:44.000 104
2012-10-22 06:20:52.000 107
2012-10-22 06:20:57.000 107
2012-10-22 06:21:01.000 107
2012-10-22 06:21:10.000 121
2012-10-22 06:21:13.000 121
2012-10-22 06:21:19.000 121

and I want to query this table to get groups of the same State_ID like this:

StartTime EndTime State_ID
2012-10-22 06:16:37.000 2012-10-22 06:16:37.000 101 -> 1 record for State_ID
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104 -> State_ID 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:19:33.000 2012-10-22 08:41:51.000 6
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104 -> another State_ID 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121

I try query like this:

select min(Value_Time) over (partition by State_ID) as StartTime, max(Value_Time) over (partition by State_ID) as EndTime, State_ID
as State_ID, Value_Time, row_number() OVER(PARTITION BY State_ID ORDER BY Value_Time) AS rk
from [SevDB].[arh].[Values]

but I'got more than 1 row for each State_ID for example:

StartTime EndTime State_ID
2012-10-22 06:16:37.000 2012-10-22 06:16:37.000 101
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:19:33.000 2012-10-22 08:41:51.000 6
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121

Can you help me?
Thx in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 08:31:52
May be this:

SELECT t.State_ID, MIN(t.Value_Time) AS MinDateTimeValue, MAX(t.Value_Time) AS MaxDateTimeValue
FROM @Tab t
OUTER APPLY (SELECT MIN(Value_Time) AS NextDate
FROM @tab
WHERE Value_Time > t.Value_Time
AND State_ID <> t.State_ID
)t1
GROUP BY t.State_ID, t1.NextDate

--
Chandu
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 10:14:20
quote:
Originally posted by Calkins

Hi.

I have problem with grouping rows in that way:

I have table of structure:

Value_Time State_ID
2012-10-22 06:16:37.000 101
2012-10-22 06:17:13.000 104
2012-10-22 06:17:27.000 104
2012-10-22 06:17:30.000 104
2012-10-22 06:17:46.000 104
2012-10-22 06:17:53.000 104
2012-10-22 06:18:00.000 107
2012-10-22 06:18:07.000 107
2012-10-22 06:18:12.000 107
2012-10-22 06:18:18.000 121
2012-10-22 06:18:21.000 121
2012-10-22 06:18:30.000 121
2012-10-22 06:18:35.000 121
2012-10-22 06:19:14.000 121
2012-10-22 06:19:19.000 121
2012-10-22 06:19:26.000 121
2012-10-22 06:19:33.000 6
2012-10-22 06:19:35.000 107
2012-10-22 06:19:42.000 107
2012-10-22 06:19:47.000 104
2012-10-22 06:19:54.000 104
2012-10-22 06:20:01.000 104
2012-10-22 06:20:37.000 104
2012-10-22 06:20:44.000 104
2012-10-22 06:20:52.000 107
2012-10-22 06:20:57.000 107
2012-10-22 06:21:01.000 107
2012-10-22 06:21:10.000 121
2012-10-22 06:21:13.000 121
2012-10-22 06:21:19.000 121

and I want to query this table to get groups of the same State_ID like this:

StartTime EndTime State_ID
2012-10-22 06:16:37.000 2012-10-22 06:16:37.000 101 -> 1 record for State_ID
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104 -> State_ID 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:19:33.000 2012-10-22 08:41:51.000 6
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104 -> another State_ID 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121

I try query like this:

select distinct min(Value_Time) over (partition by State_ID) as StartTime, max(Value_Time) over (partition by State_ID) as EndTime, State_ID
as State_ID, Value_Time, row_number() OVER(PARTITION BY State_ID ORDER BY Value_Time) AS rk from [SevDB].[arh].[Values]

but I'got more than 1 row for each State_ID for example:

StartTime EndTime State_ID
2012-10-22 06:16:37.000 2012-10-22 06:16:37.000 101
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:19:33.000 2012-10-22 08:41:51.000 6
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:00.000 2012-10-22 08:42:00.000 107
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121
2012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121

Can you help me?
Thx in advance.

Go to Top of Page

Calkins
Starting Member

5 Posts

Posted - 2012-12-13 : 10:18:43
Thx Chandu - your code seems to be working fine. Thx.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-14 : 00:53:28
quote:
Originally posted by Calkins

Thx Chandu - your code seems to be working fine. Thx.


Welcome
Did you check your output?

--
Chandu
Go to Top of Page

Calkins
Starting Member

5 Posts

Posted - 2012-12-28 : 08:01:19
Hi again.

Your earlier advice (bandi) was perfect (the output is exactly what I want) but performance of this query is a big problem. Have you any new advice? Thx in advance for support.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 09:36:14
Did you try mine? and what indexes you have for this table [SevDB].[arh].[Values]
Go to Top of Page

Calkins
Starting Member

5 Posts

Posted - 2012-12-30 : 07:55:26
Yes I've tried. The output is wrong - the same as from query
select min(Value_Time) over (partition by State_ID) as StartTime, max(Value_Time) over (partition by State_ID) as EndTime, State_ID
as State_ID, Value_Time, row_number() OVER(PARTITION BY State_ID ORDER BY Value_Time) AS rk
from [SevDB].[arh].[Values]
Go to Top of Page
   

- Advertisement -