CODEWITHSUNDEEP
sql-server
Gene
Gene

Reputation: 31

SQL Select multiple times offset from each other

Not sure if my goal is achievable in a query or if my app needs to loop through bulk results to filter for desired values. Given the following tabled data:

ID TimeStamp
1 2019-04-03 18:44:14
1 2019-04-03 18:44:13
1 2019-04-03 18:44:12
1 2019-04-03 18:34:04
1 2019-04-03 18:34:03
1 2019-04-03 18:23:26
1 2019-04-03 18:23:25
1 2019-04-03 18:23:24
1 2019-04-03 18:12:54
1 2019-04-03 18:12:53
1 2019-04-03 18:12:52

2 2019-04-03 18:45:49
2 2019-04-03 18:45:48
2 2019-04-03 18:45:47
2 2019-04-03 18:35:05
2 2019-04-03 18:35:04
2 2019-04-03 18:35:03
2 2019-04-03 18:24:02
2 2019-04-03 18:13:06
2 2019-04-03 18:13:05
2 2019-04-03 18:13:04

4 2019-04-03 18:46:24
4 2019-04-03 18:46:23
4 2019-04-03 18:46:22
4 2019-04-03 18:35:13
4 2019-04-03 18:24:10
4 2019-04-03 18:24:09
4 2019-04-03 18:13:11
4 2019-04-03 18:13:10

I can run the following query to get a single latest time for each ID. That query appears like:

SELECT ID,TimeStamp FROM (
SELECT ID,TimeStamp,
row_number() over (partition by ID order by ID, TimeStamp desc) as rn
FROM [MyData]
WHERE ID<>'0') D1
WHERE D1.rn=1

This query correctly produces a SINGLE latest read for each ID result like:

ID TimeStamp
1 2019-04-03 18:44:14
2 2019-04-03 18:45:49
4 2019-04-03 18:46:24

MY QUESTION: Is it possible to include a time value (say 60 seconds) to the query and select multiple timestamps for each ID that exceeds the entered time value? For example, I would like the query result to be:

ID TimeStamp
1 2019-04-03 18:44:14
1 2019-04-03 18:34:04
1 2019-04-03 18:23:26
1 2019-04-03 18:12:54

2 2019-04-03 18:45:49
2 2019-04-03 18:35:05
2 2019-04-03 18:24:02
2 2019-04-03 18:13:06

4 2019-04-03 18:46:24
4 2019-04-03 18:35:13
4 2019-04-03 18:24:10
4 2019-04-03 18:13:11

Note that each returned timestamp is great than 60 seconds from the previous latest value. Each ID in this scenario returns 4 records. But some IDs could return more or none at all.

Thank you in advance for any assistance helping me in the right direction. Cheers

Upvotes: 0

Views: 218

Answers (2)

AlwaysLearning
AlwaysLearning

Reputation: 8819

Make use of the LAG() and LEAD() functions, for example...

create table [dbo].[MyData] (
  [ID] int not null,
  [Timestamp] datetime
);

insert [dbo].[MyData] ([ID], [TimeStamp])
values
  (1, '2019-04-03 18:44:14'),
  (1, '2019-04-03 18:44:13'),
  (1, '2019-04-03 18:44:12'),
  (1, '2019-04-03 18:34:04'),
  (1, '2019-04-03 18:34:03'),
  (1, '2019-04-03 18:23:26'),
  (1, '2019-04-03 18:23:25'),
  (1, '2019-04-03 18:23:24'),
  (1, '2019-04-03 18:12:54'),
  (1, '2019-04-03 18:12:53'),
  (1, '2019-04-03 18:12:52'),
  (2, '2019-04-03 18:45:49'),
  (2, '2019-04-03 18:45:48'),
  (2, '2019-04-03 18:45:47'),
  (2, '2019-04-03 18:35:05'),
  (2, '2019-04-03 18:35:04'),
  (2, '2019-04-03 18:35:03'),
  (2, '2019-04-03 18:24:02'),
  (2, '2019-04-03 18:13:06'),
  (2, '2019-04-03 18:13:05'),
  (2, '2019-04-03 18:13:04'),
  (4, '2019-04-03 18:46:24'),
  (4, '2019-04-03 18:46:23'),
  (4, '2019-04-03 18:46:22'),
  (4, '2019-04-03 18:35:13'),
  (4, '2019-04-03 18:24:10'),
  (4, '2019-04-03 18:24:09'),
  (4, '2019-04-03 18:13:11'),
  (4, '2019-04-03 18:13:10');

select [ID], [TimeStamp]--, [NextTimeStamp], [Gap]
from (
  select *, [Gap] = datediff(second, [TimeStamp], [NextTimeStamp])
  from (
    select
      [ID],
      [TimeStamp],
      [rn] = row_number() over (partition by ID order by ID, TimeStamp desc),
      [NextTimeStamp] = nullif(lag([TimeStamp], 1, 0) over (partition by ID order by ID, TimeStamp desc), N'1900-01-01 00:00:00.000')
    from [dbo].[MyData]
    where ID <> 0
  ) D2
) D1
where D1.rn = 1 or [Gap] >= 60;

Which yields...

ID  TimeStamp
1   2019-04-03 18:44:14.000
1   2019-04-03 18:34:04.000
1   2019-04-03 18:23:26.000
1   2019-04-03 18:12:54.000
2   2019-04-03 18:45:49.000
2   2019-04-03 18:35:05.000
2   2019-04-03 18:24:02.000
2   2019-04-03 18:13:06.000
4   2019-04-03 18:46:24.000
4   2019-04-03 18:35:13.000
4   2019-04-03 18:24:10.000
4   2019-04-03 18:13:11.000

If you uncomment the [NextTime], [Gap] you can see why the additional rows were added - the gaps being much greater than 60 seconds.

Upvotes: 1

Leszek Mazur
Leszek Mazur

Reputation: 2531

You can use recursive CTE to do taht:

WITH CTE AS (
    SELECT MD.[ID], MAX(MD.[TimeStamp]) AS [TimeStamp], MAX(N.[TimeStamp]) AS [NextTimestamp]
    FROM [MyData] MD
    LEFT JOIN [MyData] N
        ON MD.[ID] = N.[ID] AND N.[TimeStamp] <= DATEADD(SECOND, -60, MD.[TimeStamp])
    GROUP BY MD.[ID]
    UNION ALL
    SELECT MD.[ID], MD.[TimeStamp],  MAX(N.[TimeStamp]) OVER (PARTITION BY N.[ID]) AS [NextTimestamp]
    FROM [MyData] MD
    INNER JOIN CTE C
        ON C.[NextTimestamp] = MD.[TimeStamp]
        AND C.[ID] = MD.[ID]
    INNER JOIN [MyData] N
        ON MD.[ID] = N.[ID] AND N.[TimeStamp] <= DATEADD(SECOND, -60, MD.[TimeStamp])
)

SELECT DISTINCT [ID], [TimeStamp]
FROM CTE
ORDER BY [ID], [TimeStamp] DESC

Upvotes: 1

Related Questions