-1

I want to add 60 seconds to the date value from the first date and return the data if it exists in that interval like this

datetime value
2021-05-02 12:20:1 5
2021-05-02 12:20:15 8
2021-05-02 12:20:26 12
2021-05-02 12:21:1 63
2021-05-02 12:21:23 90
2021-05-02 12:21:12 73
2021-05-02 12:22:3 39
2021-05-02 12:23:12 3
2021-05-02 12:28:23 74
2021-05-02 12:28:26 38
2021-05-02 12:28:26 72
2021-05-02 12:28:28 12
2021-05-02 12:28:30 69

RESULT like :

datetime value
2021-05-02 12:20:1 5
2021-05-02 12:21:1 63
2021-05-02 12:22:3 39
2021-05-02 12:23:12 3
2021-05-02 12:28:23 74

I try by this code But I did not get the right Result

        WITH cte AS (
    SELECT *, 
          ROW_NUMBER() OVER (PARTITION BY datetime ORDER BY DATETIME)-  ROW_NUMBER() OVER (PARTITION BY dateadd(second,60,datetime) ORDER BY DATETIME) as g
    FROM Inputs
                    )
                SELECT DateTime
                    FROM cte
                    group by g
2
  • Can you construct a fiddle (dbfiddle.uk) with your tables and data - also, a more detailled explanation of your requirement would also help! Some of the data points aren't even real times!
    – Vérace
    Sep 13 at 14:37
  • Take a look at minimal reproducible example and add the requisite details to your question.
    – Hannah Vernon
    Sep 13 at 15:11
1

You can truncate the date to the minute and partition by that value

WITH cte AS (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY DATEADD(minute, DATEDIFF(minute, 0, t.datetime), 0)
        ORDER BY t.datetime) AS rn
    FROM YourTable t
)
SELECT
  datetime,
  value
FROM cte
WHERE rn = 1;
2
  • HOW TO PARTITION BY 15 MINUTE OR 10 MINUTE? Sep 14 at 5:15
  • 1
    DATEADD(minute, DATEDIFF(minute, 0, t.datetime) / 15 * 15, 0) Sep 14 at 9:30

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.