3

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

3
  • What version of SQL Server?
    – Dan Guzman
    Jul 17 '15 at 11:23
  • sql server-2000
    – m409
    Jul 17 '15 at 11:30
  • Unfortunately, CTEs (WITH) are not available in SQL Server 2000, which would be a building block for doing this in a single query. Be aware that the SQL Server 2000 support lifecycle has ended.
    – Dan Guzman
    Jul 17 '15 at 11:47
4

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).

-1

I am not much sure on SQL Server, but this works with PostgreSQL.

Using Recursive CTE to start with the day, more comments are inline.

    create table amn1
    (
    id int,
    upd timestamp,
    price float);
     insert into amn1 values
    (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);

    WITH RECURSIVE dtls (ID, upd, price) AS (
        SELECT
            amn1.ID,
            amn1.upd,
            price
        FROM
            ( 
                SELECT ID, MIN (upd) AS upd 
                FROM amn1 
                WHERE upd >= now() - INTERVAL '1 day'--To Start from today
                GROUP BY ID) T
        INNER JOIN amn1 ON (amn1. ID = T . ID AND amn1.upd = T .upd)--To get today's values
        UNION ALL
            (
                SELECT amn1. ID, amn1.upd, amn1.price 
                FROM
                    amn1
                INNER JOIN dtls ON (amn1. ID = dtls. ID AND amn1.upd >= (dtls.upd + INTERVAL '5 mins'))--Find next earliest updated value
                ORDER BY amn1.upd
                LIMIT 1--Limit with one
            )
    )
    SELECT * FROM dtls

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.