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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 Repetition of groups
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Calkins
Starting Member

5 Posts

Posted - 12/13/2012 :  08:16:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/13/2012 :  08:31:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/13/2012 :  10:14:20  Show Profile  Reply with Quote
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 - 12/13/2012 :  10:18:43  Show Profile  Reply with Quote
Thx Chandu - your code seems to be working fine. Thx.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/14/2012 :  00:53:28  Show Profile  Reply with Quote
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 - 12/28/2012 :  08:01:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  09:36:14  Show Profile  Reply with Quote
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 - 12/30/2012 :  07:55:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next 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.14 seconds. Powered By: Snitz Forums 2000