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: Wednesday, April 16, 2014 1:34 AM
Points: 3, Visits: 132
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: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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
Posted Sunday, December 30, 2012 1:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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