Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving groups Expand / Collapse
Author
Message
Posted Sunday, December 30, 2012 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:06 AM
Points: 3, Visits: 136
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
) 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

Post #1401257
Posted Sunday, December 30, 2012 12:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 1,812, Visits: 21,234
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



Post #1401264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse