Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Moving groups
Add to briefcase

Moving groups Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Damian Calkins
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: Friday, May 10, 2013 3:24 AM
Points: 2, Visits: 108
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
Mark-101232
Posted Sunday, December 30, 2012 12:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 1,500, Visits: 18,194
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.

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1401264
CELKO
Posted Sunday, December 30, 2012 1:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Is there any reason for all that dialect, when T-SQL has ANSI/ISO Standard features today? Why is the value (which is a reserved word in ANSI/ISO Standard SQL as well as too vague for a data element name) a constant? Leading zeroes are a good idea in a tag number; it lets the reader see that it is an encoding and not a computational value. Do you really need decimal seconds? You can get nanosecond now if you really need it. Here is my guess at DDL, ISO-11179 names, etc.

CREATE TABLE Samplings
(state_id CHAR(3) NOT NULL,
sampling_timestamp DATETIME2(0) NOT NULL,
PRIMARY KEY (state_id, sampling_timestamp));

INSERT INTO Samplings
VALUES ('13', '2012-10-23 00:00:00'),
('13', '2012-10-23 00:00:01'),
('05', '2012-10-23 00:00:12'),
('13', '2012-10-23 00:43:59'),
('13', '2012-10-23 04:00:11'),
('13', '2012-10-23 04:01:20'),
('05', '2012-10-23 05:00:11'),
('05', '2012-10-23 05:23:20'),
('05', '2012-10-23 05:23:33'),
('05', '2012-10-23 05:23:45'),
('13', '2012-10-23 05:23:46'),
('13', '2012-10-23 05:23:58'),
('05', '2012-10-23 05:23:58'),
('05', '2012-10-23 05:24:10'),
('27', '2012-10-23 05:24:11'),
('27', '2012-10-23 05:24:25'),
('27', '2012-10-23 05:24:26'),
('27', '2012-10-23 05:24:40'),
('05', '2012-10-24 00:00:00');

SELECT S1.state_id,
MIN(S1.sampling_timestamp) AS start_time,
MAX(S1.sampling_timestamp) AS stop_time
FROM (SELECT state_id, sampling_timestamp,
ROW_NUMBER() OVER (ORDER BY sampling_timestamp)
-ROW_NUMBER()
OVER (PARTITION BY state_id ORDER BY sampling_timestamp)
AS grp_nbr
FROM Samplings) AS S1
GROUP BY S1.state_id, S1.grp_nbr
ORDER BY start_time;

Look that the code for S1; it is a common idiom.
Here is the output:

13 2012-10-23 00:00:00 2012-10-23 00:00:01
05 2012-10-23 00:00:12 2012-10-23 00:00:12
13 2012-10-23 00:43:59 2012-10-23 04:01:20
05 2012-10-23 05:00:11 2012-10-23 05:23:45
13 2012-10-23 05:23:46 2012-10-23 05:23:58
05 2012-10-23 05:23:58 2012-10-23 05:24:10
27 2012-10-23 05:24:11 2012-10-23 05:24:40
05 2012-10-24 00:00:00 2012-10-24 00:00:00

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1401267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.