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)
 Repetition of groups		  New Topic  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
1444 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
7173 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
1444 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
7173 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  
 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