Clever Geek Handbook

Getting the row closest to time for each row in another table

Problem:

Hi I have a table with historical data from a sensor group and I am trying to get the row for each historical data record closest in time to the desired time. For example, I want the recording to be as close as possible to every minute.

I simplified the problem in the following way, which if I can solve, I can use to post my general solution:

Take two tables like this:

CREATE TABLE [TempDataTable](
[DataIndex] [int] IDENTITY(0,2) NOT NULL,
[DataName] [varchar](40) NOT NULL,
[DataValue] [decimal](10,2) NOT NULL,
[DataTimeStamp] [datetime2](7)
)

CREATE TABLE [TempTargetTable](
[TargetIndex] [int] IDENTITY(1,2) NOT NULL,
[TargetTime] [datetime2](7)
)

For each line in TempTargetTable

, get the line in TempDataTable

c TempDataTable.DataTimeStamp

closest toTempTargetTable.TargetTime

If I can do this, I'm sure I can figure out the rest, but I don't understand how to get this first step to work. For ease of testing the code, I can provide the following, which populates both tables with some test data:

Useful test data:

INSERT INTO [TempDataTable]
    ([DataName],
    [DataValue],
    [DataTimeStamp])
VALUES
    ('Sensor',0,    '2017-01-01 00:00:00'),
    ('Sensor',0.5,  '2017-01-01 00:00:17'),
    ('Sensor',1,    '2017-01-01 00:01:03'),
    ('Sensor',1.5,  '2017-01-01 00:01:30'),
    ('Sensor',1.5,  '2017-01-01 00:01:38'),
    ('Sensor',2,    '2017-01-01 00:02:01'),
    ('Sensor',2.5,  '2017-01-01 00:02:15'),
    ('Sensor',3,    '2017-01-01 00:02:56'),
    ('Sensor',3.5,  '2017-01-01 00:03:27'),
    ('Sensor',4,    '2017-01-01 00:04:01'),
    ('Sensor',5,    '2017-01-01 00:05:00'),
    ('Sensor',5.5,  '2017-01-01 00:05:15'),
    ('Sensor',5.5,  '2017-01-01 00:05:46'),
    ('Sensor',6,    '2017-01-01 00:06:10'),
    ('Sensor',7,    '2017-01-01 00:06:57'),
    ('Sensor',7.5,  '2017-01-01 00:07:13'),
    ('Sensor',8,    '2017-01-01 00:08:01'),
    ('Sensor',9,    '2017-01-01 00:09:03')

INSERT INTO [TempTargetTable]
    ([TargetTime])
VALUES
    ('2017-01-01 00:00:00'),
    ('2017-01-01 00:01:00'),
    ('2017-01-01 00:02:00'),
    ('2017-01-01 00:03:00'),
    ('2017-01-01 00:04:00'),
    ('2017-01-01 00:05:00'),
    ('2017-01-01 00:06:00'),
    ('2017-01-01 00:07:00'),
    ('2017-01-01 00:08:00'),
    ('2017-01-01 00:09:00')
+3


source to share


3 answers


For the current problem you are posting (simplified), I did the following:

Crosshair joined tables to make a difference for every target time, with every existing data timestamp. Then I applied a function DENSE_RANK

that will provide a ranking for each TargetTime, and then only those records with a minimum difference in milliseconds will be selected.



You can find a working solution here.

 select TargetIndex, TargetTime, DataIndex, DataName, DataValue, DataTimeStamp
 from
  (
   select t.*, DENSE_RANK() OVER(PARTITION BY t.targetindex ORDER BY t.diff) as Rank
   from 
    (
      select tg.targetindex, tg.targettime, t.dataindex, t.dataname, t.datavalue, t.datatimestamp, abs(datediff(ms, tg.TargetTime, t.DataTimeStamp)) diff
      from TempDataTable t cross join TempTargetTable tg
    ) t
 ) f 
 where Rank = 1
+1


source


If you want the first entry for every calendar minute, you can use row_number()

:



select tdt.*
from (select tdt.*,
             row_number() over (partition by format(DataTimeStamp, 'yyyy-MM-dd HH:mm')
                                order by DataTimeStamp asc
                               ) as seqnum
      from TempDataTable tdt
     ) tdt
where seqnum = 1;
0


source


if I read your question correctly, you want the closest entry even if it is in the previous minute. If so, you may receive this request. I did it in a few steps, so you can easily follow (hopefully)

What I've done:

  • find the closest minute, if seconds> = 30 are rounded to the next minute, keep the actual minute
  • calculate the difference in seconds and find the paragraph value
  • get closest value to time point

Request

SELECT tempd.TargetTime, tdfinal.DataName, tdfinal.DataValue, tdfinal.DataTimeStamp
FROM @TempTargetTable as tempd
LEFT OUTER JOIN
   (SELECT tdseconds.*, ROW_NUMBER() OVER(PARTITION BY closestMinute ORDER BY secondDiff) AS r
    FROM (SELECT td.*, ABS(DATEDIFF(SECOND, DataTimeStamp, closestMinute)) AS secondDiff
          FROM (SELECT DataName,DataValue,DataTimeStamp,
                  CONVERT(DATETIME,CONVERT(DATE, datatimestamp, 121)) + 
                  CONVERT (DATETIME,TIMEFROMPARTS(DATEPART(HOUR, datatimestamp), 
                             CASE WHEN DATEPART(SECOND, DataTimeStamp) >= 30 
                                    THEN DATEPART(MINUTE, DATATimeStamp) + 1 
                                    ELSE DATEPART(MINUTE, DATATimeStamp) END, 0,0,0), 121) AS closestMinute
                FROM @TempDataTable ) AS td
          ) AS tdseconds
   ) AS tdfinal
ON tdfinal.closestMinute = tempd.TargetTime
WHERE tdfinal.r = 1

Result

TargetTime               DataName  DataValue  DataTimeStamp
2017-01-01 00:00:00.000  Sensor    0.00       2017-01-01 00:00:00.000
2017-01-01 00:01:00.000  Sensor    1.00       2017-01-01 00:01:03.000
2017-01-01 00:02:00.000  Sensor    2.00       2017-01-01 00:02:01.000
2017-01-01 00:03:00.000  Sensor    3.00       2017-01-01 00:02:56.000
2017-01-01 00:04:00.000  Sensor    4.00       2017-01-01 00:04:01.000
2017-01-01 00:05:00.000  Sensor    5.00       2017-01-01 00:05:00.000
2017-01-01 00:06:00.000  Sensor    6.00       2017-01-01 00:06:10.000
2017-01-01 00:07:00.000  Sensor    7.00       2017-01-01 00:06:57.000
2017-01-01 00:08:00.000  Sensor    8.00       2017-01-01 00:08:01.000
2017-01-01 00:09:00.000  Sensor    9.00       2017-01-01 00:09:03.000
0


source






More articles:


All Articles