Moving groups

• Damian Calkins

  SSC Rookie

  Points: 27

  More actions

  December 30, 2012 at 9:57 am

  #266649

  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_IDValue_TimeValue

  132012-10-23 00:00:00.0000

  132012-10-23 00:00:01.0000

  52012-10-23 00:00:12.7270

  132012-10-23 00:43:59.0000

  132012-10-23 04:00:11.4670

  132012-10-23 04:01:20.0000

  52012-10-23 05:00:11.4300

  52012-10-23 05:23:20.0000

  52012-10-23 05:23:33.2970

  52012-10-23 05:23:45.8030

  132012-10-23 05:23:46.0000

  132012-10-23 05:23:58.0000

  52012-10-23 05:23:58.1600

  52012-10-23 05:24:10.9900

  272012-10-23 05:24:11.0000

  272012-10-23 05:24:25.9730

  272012-10-23 05:24:26.0000

  272012-10-23 05:24:40.0000

  52012-10-24 00:00:00.0000

  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_IDMinDateTimeForStateMaxDateTimeForState

  132012-10-23 00:00:00.0002012-10-23 00:00:01.000

  52012-10-23 00:00:12.7272012-10-23 00:00:12.727

  132012-10-23 00:43:59.0002012-10-23 04:01:20.000

  52012-10-23 05:00:11.4302012-10-23 05:23:45.803

  132012-10-23 05:23:46.0002012-10-23 05:23:58.000

  52012-10-23 05:23:58.1602012-10-23 05:24:10.990

  272012-10-23 05:24:11.0002012-10-23 05:24:40.000

  52012-10-24 00:00:00.0002012-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

  ) t1

  GROUP BY t.State_ID, t1.NextDate

  ORDER BY MinDateTimeForState

  but the performance of this solution is very very bad 🙁 (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

  ) t1

  GROUP BY t.State_ID, t1.NextDate

  ORDER BY MinDateTimeForState

  DROP TABLE #tmp_GridResults_1

  GO

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  December 30, 2012 at 12:11 pm

  #1572408

  Try this, its a "Gaps and Islands" problem, you can find information about it in Jeff Modens excellent article here[/url]

  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