Sql-server – How to get 5min date interval from stock data

sql serversql-server-2000

i have a stock data of comapy as shown below 

id     update time                price
5    2015-07-17 09:02:00.000    65.5
5    2015-07-17 09:03:00.000    65.5
5    2015-07-17 09:05:00.000    65.5
5    2015-07-17 09:06:00.000    66
5    2015-07-17 09:07:00.000    66
5    2015-07-17 09:08:00.000    66
5    2015-07-17 09:16:00.000    66.5
5    2015-07-17 09:17:00.000    66.3
5    2015-07-17 09:18:00.000    66.25
5    2015-07-17 09:19:00.000    66.3
5    2015-07-17 09:20:00.000    67.3
5    2015-07-17 09:21:00.000    67.5

I want 5min interval date and if next 5min date is not there query should select latest date

i.e 9.02,9.07,9.12 <–not there so it should select 9.16,then 9.21 so on

5 is company id then updTime at last price

Best Answer

For recent versions of SQL-Server that support CTEs (and not the obsolete 2000):

Using a CTE and copying a trick of @Paul White in this answer (to get TOP in the recursive part of the CTE): How to recursively find gaps where 90 days passed, between rows

WITH CTE AS
(
    -- Anchor:
    -- Start with the earliest date in the table
    SELECT TOP (1)
        T.id, T.update_time, T.price
    FROM tableX AS T
    ORDER BY
        T.update_time

    UNION ALL

    -- Recursive part   
    SELECT
        SQ1.id, SQ1.update_time, SQ1.price
    FROM 
    (
        -- Recursively find the earliest date that is 
        -- more than 5 minutes after the "current" date
        -- and set the new date as "current".
        -- ROW_NUMBER + rn = 1 is a trick to get
        -- TOP in the recursive part of the CTE
        SELECT
            T.id, T.update_time, T.price,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.update_time)
        FROM CTE
        JOIN tableX AS T
            ON T.update_time >= DATEADD(minute, 5, CTE.update_time)
    ) AS SQ1
    WHERE
        SQ1.rn = 1
)
SELECT id, update_time, price 
FROM CTE ;

A cursor solution, that works in older versions (I don't have a 2000 instance to test and the following will probably need adjustments). Modified from another answer, in the same question How to recursively find gaps where 90 days passed, between rows:

(first, some needed tables and variables):

-- a table to hold the results
DECLARE @cd TABLE
(   id INT NOT NULL,                        -- adjust types
    update_time DATETIME PRIMARY KEY,       -- according to 
    price DECIMAL(10,2) NOT NULL            -- your columns
);

-- some variables
DECLARE
    @id INT,
    @update_time DATETIME,
    @price DECIMAL(10,2),

    @diff INT,
    @previous_update_time DATETIME = '1900-01-01 00:00:00' ;

The actual cursor:

-- declare the cursor
DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT id, update_time, price
      FROM tableX
      ORDER BY update_time ;

-- using the cursor to fill the @cd table
OPEN c ;

FETCH NEXT FROM c INTO @id, @update_time, @price ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @diff = DATEDIFF(minute, @previous_update_time, @update_time) ;

    IF @diff >= 5
      BEGIN
        INSERT @cd (id, update_time, price)
            VALUES (@id, @update_time, @price) ;

        SET @previous_update_time = @update_time ;
      END ;

    FETCH NEXT FROM c INTO @id, @update_time, @price ;
END

CLOSE c;
DEALLOCATE c;

And getting the results:

-- get the results
SELECT id, update_time, price
    FROM @cd
    ORDER BY update_time ;

Tested at SQLfiddle (in 2008 version).

Related Question