Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving groups


Moving groups

Author
Message
Damian Calkins
Damian Calkins
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 138
Hi.

I want to group records by moving group by one field for example:
I have table with measurements with structure: Value_Time, Value, State_ID and sample data:


State_ID   Value_Time   Value
13   2012-10-23 00:00:00.000   0
13   2012-10-23 00:00:01.000   0
5   2012-10-23 00:00:12.727   0
13   2012-10-23 00:43:59.000   0
13   2012-10-23 04:00:11.467   0
13   2012-10-23 04:01:20.000   0
5   2012-10-23 05:00:11.430   0
5   2012-10-23 05:23:20.000   0
5   2012-10-23 05:23:33.297   0
5   2012-10-23 05:23:45.803   0
13   2012-10-23 05:23:46.000   0
13   2012-10-23 05:23:58.000   0
5   2012-10-23 05:23:58.160   0
5   2012-10-23 05:24:10.990   0
27   2012-10-23 05:24:11.000   0
27   2012-10-23 05:24:25.973   0
27   2012-10-23 05:24:26.000   0
27   2012-10-23 05:24:40.000   0
5   2012-10-24 00:00:00.000   0



I want to query with result like this (moving group of field State_ID and moving Min and Max of field Value_time for grouped record). It should be like this:


State_ID   MinDateTimeForState   MaxDateTimeForState
13   2012-10-23 00:00:00.000   2012-10-23 00:00:01.000
5   2012-10-23 00:00:12.727   2012-10-23 00:00:12.727
13   2012-10-23 00:43:59.000   2012-10-23 04:01:20.000
5   2012-10-23 05:00:11.430   2012-10-23 05:23:45.803
13   2012-10-23 05:23:46.000   2012-10-23 05:23:58.000
5   2012-10-23 05:23:58.160   2012-10-23 05:24:10.990
27   2012-10-23 05:24:11.000   2012-10-23 05:24:40.000
5   2012-10-24 00:00:00.000   2012-10-24 00:00:00.000



I found query that gives me my desired result:

SELECT
   t.State_ID AS State_ID,
   MIN(t.Value_Time) AS MinDateTimeForState,
   MAX(t.Value_Time) AS MaxDateTimeForState
   --,DATEDIFF(ss, MIN(t.Value_Time), MAX(t.Value_Time)) AS Duration
FROM #tmp_GridResults_1 t
OUTER APPLY (SELECT MIN(Value_Time) AS NextDate
       FROM #tmp_GridResults_1
         WHERE State_ID <> t.State_ID AND Value_Time > t.Value_Time
         Wink t1
GROUP BY t.State_ID, t1.NextDate
ORDER BY MinDateTimeForState



but the performance of this solution is very very bad Sad (119 table scans for only 19 records). Can anybody help me rewrite query that will be more efficient.
Below sample data and example of my solution. Thx for your help in advance.

---------------   #tmp_GridResults_1   ---------------
SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'13' AS [State_ID], N'2012-10-23 00:00:00.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 00:00:01.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 00:00:12.727' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 00:43:59.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 04:00:11.467' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 04:01:20.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:00:11.430' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:20.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:33.297' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:45.803' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 05:23:46.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 05:23:58.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:58.160' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:24:10.990' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:11.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:25.973' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:26.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:40.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-24 00:00:00.000' AS [Value_Time], N'0' AS [Value] ) t;
SELECT [State_ID], [Value_Time], [Value]
FROM #tmp_GridResults_1
ORDER BY Value_Time

         SELECT
            --DISTINCT
            t.State_ID AS State_ID,
            --MIN(t.[Value]) as [MinValue],
            --MAX(t.[Value]) as [MaxValue],
            MIN(t.Value_Time) AS MinDateTimeForState,
            MAX(t.Value_Time) AS MaxDateTimeForState
            --,DATEDIFF(ss, MIN(t.Value_Time), MAX(t.Value_Time)) AS Duration
         FROM #tmp_GridResults_1 t
         OUTER APPLY (SELECT MIN(Value_Time) AS NextDate
         FROM #tmp_GridResults_1
         WHERE State_ID <> t.State_ID AND Value_Time > t.Value_Time
         Wink t1
         GROUP BY t.State_ID, t1.NextDate
         ORDER BY MinDateTimeForState


DROP TABLE #tmp_GridResults_1
GO


Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 22778
Try this, its a "Gaps and Islands" problem, you can find information about it in Jeff Modens excellent article here

WITH CTE AS (
SELECT [State_ID], [Value_Time], [Value],
ROW_NUMBER() OVER(ORDER BY [Value_Time]) AS rn1,
ROW_NUMBER() OVER(PARTITION BY [State_ID] ORDER BY [Value_Time]) AS rn2
FROM #tmp_GridResults_1)
SELECT [State_ID],
    MIN([Value_Time]) AS MinDateTimeForState,
    MAX([Value_Time]) AS MaxDateTimeForState
FROM CTE
GROUP BY [State_ID],rn2-rn1
ORDER BY MinDateTimeForState;



Good job of posting DDL and sample data, thanks.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search