none
SQL Server is not retrieving data for every 15 min interval of time RRS feed

  • Question

  • Hi All,

    I have a query to return the rows starting with min timestamps and giving 15 min time interval between. This query is retieving results but if the first timestamp is retrieving some value which is not divided by 15 it displays the second row which is divisible by 15 and from there it gives 15min time interval. But I need the timestamp to be displayed by 15min each starting from first row:

    My query is looks as:

      

    SELECT (SELECTCONVERT(VARCHAR(16),MIN([col1]),120))ASRext FROM table1wherecolum3 = '%hght%'

    GROUPBYDATEPART(YEAR,[col1]), DATEPART(MONTH,[col1]),DATEPART(DAY,[col1]),

    DATEPART(HOUR,[col1]),(DATEPART(MINUTE,[EVT_TS])/15)  ORDERBYRext

    Result as:

    Rext
    2019-07-19 06:59
    2019-07-19 07:00
    2019-07-19 07:15
    2019-07-19 07:30
    2019-07-19 07:45
    2019-07-19 08:00
    2019-07-19 08:15
    2019-07-19 08:30
    2019-07-19 08:45
    2019-07-19 09:00

    But expected result as:

    2019-07-19 06:59
    2019-07-19 07:14
    2019-07-19 07:29
    2019-07-19 07:44
    2019-07-19 07:59
    2019-07-19 08:14
    2019-07-19 08:29
    2019-07-19 08:44
    2019-07-19 08:59
    2019-07-19 09:14

    Could someone help me resolve this issue

    Friday, July 19, 2019 5:27 PM

Answers

  • Here is an example you can work from:

    CREATE TABLE #test(a datetime2(3) NOT NULL)
    INSERT #test(a) VALUES
    ('2017-01-01 06:59'), ('2017-01-01 07:00'), ('2017-01-01 07:12'), ('2017-01-01 07:14'), ('2017-01-01 07:16'), ('2017-01-01 07:28'), ('2017-01-01 07:29'), ('2017-01-01 07:30'), ('2017-01-01 07:31'), ('2017-01-01 07:59')
    go
    ; WITH diffs AS (
       SELECT a, first_value(a) OVER (ORDER BY a) as ref_value,           datediff(minute, first_value(a) OVER(ORDER BY a), a) /15 AS no_of_quarters
       FROM   #test
    )
    SELECT dateadd(minute, 15*no_of_quarters, ref_value), COUNT(*)
    FROM   diffs
    GROUP  BY dateadd(minute, 15*no_of_quarters, ref_value)
    go
    DROP TABLE #test


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 19, 2019 9:21 PM
    Moderator