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.
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_ID2012-10-22 06:16:37.000 1012012-10-22 06:17:13.000 1042012-10-22 06:17:27.000 1042012-10-22 06:17:30.000 1042012-10-22 06:17:46.000 1042012-10-22 06:17:53.000 1042012-10-22 06:18:00.000 1072012-10-22 06:18:07.000 1072012-10-22 06:18:12.000 1072012-10-22 06:18:18.000 1212012-10-22 06:18:21.000 1212012-10-22 06:18:30.000 1212012-10-22 06:18:35.000 1212012-10-22 06:19:14.000 1212012-10-22 06:19:19.000 1212012-10-22 06:19:26.000 1212012-10-22 06:19:33.000 62012-10-22 06:19:35.000 1072012-10-22 06:19:42.000 1072012-10-22 06:19:47.000 1042012-10-22 06:19:54.000 1042012-10-22 06:20:01.000 1042012-10-22 06:20:37.000 1042012-10-22 06:20:44.000 1042012-10-22 06:20:52.000 1072012-10-22 06:20:57.000 1072012-10-22 06:21:01.000 1072012-10-22 06:21:10.000 1212012-10-22 06:21:13.000 1212012-10-22 06:21:19.000 121and I want to query this table to get groups of the same State_ID like this:StartTime EndTime State_ID2012-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 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:19:33.000 2012-10-22 08:41:51.000 62012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104 -> another State_ID 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121I 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_ID2012-10-22 06:16:37.000 2012-10-22 06:16:37.000 1012012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:19:33.000 2012-10-22 08:41:51.000 62012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121Can 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 tOUTER APPLY (SELECT MIN(Value_Time) AS NextDate FROM @tab WHERE Value_Time > t.Value_Time AND State_ID <> t.State_ID )t1GROUP BY t.State_ID, t1.NextDate--Chandu |
|
|
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_ID2012-10-22 06:16:37.000 1012012-10-22 06:17:13.000 1042012-10-22 06:17:27.000 1042012-10-22 06:17:30.000 1042012-10-22 06:17:46.000 1042012-10-22 06:17:53.000 1042012-10-22 06:18:00.000 1072012-10-22 06:18:07.000 1072012-10-22 06:18:12.000 1072012-10-22 06:18:18.000 1212012-10-22 06:18:21.000 1212012-10-22 06:18:30.000 1212012-10-22 06:18:35.000 1212012-10-22 06:19:14.000 1212012-10-22 06:19:19.000 1212012-10-22 06:19:26.000 1212012-10-22 06:19:33.000 62012-10-22 06:19:35.000 1072012-10-22 06:19:42.000 1072012-10-22 06:19:47.000 1042012-10-22 06:19:54.000 1042012-10-22 06:20:01.000 1042012-10-22 06:20:37.000 1042012-10-22 06:20:44.000 1042012-10-22 06:20:52.000 1072012-10-22 06:20:57.000 1072012-10-22 06:21:01.000 1072012-10-22 06:21:10.000 1212012-10-22 06:21:13.000 1212012-10-22 06:21:19.000 121and I want to query this table to get groups of the same State_ID like this:StartTime EndTime State_ID2012-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 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:19:33.000 2012-10-22 08:41:51.000 62012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:17:13.000 2012-10-22 08:42:56.000 104 -> another State_ID 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121I 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_ID2012-10-22 06:16:37.000 2012-10-22 06:16:37.000 1012012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:19:33.000 2012-10-22 08:41:51.000 62012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:17:13.000 2012-10-22 08:42:56.000 1042012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:00.000 2012-10-22 08:42:00.000 1072012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 1212012-10-22 06:18:18.000 2012-10-22 08:41:45.000 121Can you help me?Thx in advance.
|
|
|
Calkins
Starting Member
5 Posts |
Posted - 2012-12-13 : 10:18:43
|
Thx Chandu - your code seems to be working fine. Thx. |
|
|
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.
WelcomeDid you check your output?--Chandu |
|
|
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. |
|
|
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] |
|
|
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_IDas State_ID, Value_Time, row_number() OVER(PARTITION BY State_ID ORDER BY Value_Time) AS rkfrom [SevDB].[arh].[Values] |
|
|
|
|
|
|
|