Intervals of values
-
Hi,
I have a table:
CREATE TABLE Test
(
Index INT,
Value INT,
Stamp DATETIME
)
Data from the table:
Index Value Stamp
=====================================
1 1 2007-08-03 08:00:00.000
2 1 2007-08-05 08:00:00.000
3 3 2007-08-11 08:00:00.000
4 3 2007-08-16 08:00:00.000
5 3 2007-08-19 08:00:00.000
6 1 2007-08-23 08:00:00.000
7 1 2007-08-25 08:00:00.000
8 4 2007-08-29 08:00:00.000
9 3 2007-08-33 08:00:00.000
10 3 2007-08-37 08:00:00.000
I need a query that get how long last a value:
Values Start End
==========================================================
1 2007-08-03 08:00:00.000 2007-08-05 08:00:00.000
3 2007-08-11 08:00:00.000 2007-08-19 08:00:00.000
1 2007-08-23 08:00:00.000 2007-08-25 08:00:00.000
4 2007-08-29 08:00:00.000 2007-08-29 08:00:00.000
3 2007-08-33 08:00:00.000 2007-08-37 08:00:00.000
Thanks,
ioani
-
This was removed by the editor as SPAM
-
It's an "island" problem.
Try this:
--setup
DECLARE @test-2 TABLE
(
[Index] INT,
[Value] INT,
[Stamp] DATETIME
)
INSERT INTO @test-2 VALUES (1, 1, '2007-08-03 08:00:00.000');
INSERT INTO @test-2 VALUES (2, 1, '2007-08-05 08:00:00.000');
INSERT INTO @test-2 VALUES (3, 3, '2007-08-11 08:00:00.000');
INSERT INTO @test-2 VALUES (4, 3, '2007-08-16 08:00:00.000');
INSERT INTO @test-2 VALUES (5, 3, '2007-08-19 08:00:00.000');
INSERT INTO @test-2 VALUES (6, 1, '2007-08-23 08:00:00.000');
INSERT INTO @test-2 VALUES (7, 1, '2007-08-25 08:00:00.000');
INSERT INTO @test-2 VALUES (8, 4, '2007-08-29 08:00:00.000');
INSERT INTO @test-2 VALUES (9, 3, '2007-08-30 08:00:00.000');
INSERT INTO @test-2 VALUES (10, 3, '2007-08-31 08:00:00.000');
--solution
WITH IslandData AS (
SELECT *,
Island_id = ROW_NUMBER() OVER (ORDER BY Stamp) -
ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Stamp)
FROM @test-2
),
RankedData AS (
SELECT *,
RankInIsland = ROW_NUMBER() OVER (PARTITION BY island_id ORDER BY Stamp),
ReverseRankInIsland = ROW_NUMBER() OVER (PARTITION BY island_id ORDER BY Stamp DESC)
FROM IslandData
)
SELECT A.Value, A.Stamp AS Start, B.Stamp AS [End]
FROM RankedData AS A
INNER JOIN RankedData AS B
ON A.island_id = B.island_id
AND B.ReverseRankInIsland = 1
WHERE A.RankInIsland = 1
ORDER BY A.Stamp
BTW, your sample data looks wrong (does August have 37 days?).
-- Gianluca Sartori
-
WITH CTE AS (
SELECT [Index],Value,Stamp,
ROW_NUMBER() OVER(ORDER BY [Index])AS rn1,
ROW_NUMBER() OVER(PARTITION BY Value ORDER BY [Index])AS rn2
FROM Test)
SELECT Value,MIN(Stamp) AS Start, MAX(Stamp) AS [End]
FROM CTE
GROUP BY Value,rn1-rn2
ORDER BY MIN(Stamp);
____________________________________________________
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
-
Mark-101232 (10/4/2011)
WITH CTE AS (
SELECT [Index],Value,Stamp,
ROW_NUMBER() OVER(ORDER BY [Index])AS rn1,
ROW_NUMBER() OVER(PARTITION BY Value ORDER BY [Index])AS rn2
FROM Test)
SELECT Value,MIN(Stamp) AS Start, MAX(Stamp) AS [End]
FROM CTE
GROUP BY Value,rn1-rn2
ORDER BY MIN(Stamp);
Ah! I knew I was missing something!
Thank you very much, Mark!
-- Gianluca Sartori
-
Sorry, for putting wrong sample data. The data should be between 1 and 31 August.
-
Thank you,
Very nice solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply