[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

SQL update matching first record with closest time

Hi
I have following sample data in different tables
HistorianData
     TIMESTAMP                  FieldX
1   15/10/08  00:00:00     NULL
2   15/10/08  00:01:00     NULL
3   15/10/08  00:02:00     NULL
...
X     15/10/09  23:59:00     NULL
X+1 16/10/09  00:00:00     NULL
X+2  16/10/09 00:01:00     NULL
XXX...      16/10/09  00:02:00

and so on...a date/time field with every minute and 0 seconds

I have another table DataDump with data like this

       TIMESTAMP                  FieldX
9     15/10/08  00:00:10       1
10   15/10/08  00:00:20       2
11   15/10/08  00:00:40       90
12   16/10/08  00:01:16       64
13   16/10/08  00:01:59       20
...
X     16/10/08  00:02:20       123


I want to set the FieldX of HistorianData to be FieldX of DataDump . Because in DataDump the TIMESTAMP isn't rounded down every minute so often there are not exact matches with HistorianData.
In the match I want to round the first record of every minute DOWN so that there is always a match, so in above I want

1 would be set to record ID 9
2 would be set to record ID 12
XXX would be set to record ID X

There are more than one record per minute in DataDump , I only want to match the closest record and ignore others.

Thanks in advance for help with transact-SQL.  If it helps, any records of TableX records can be deleted or modified, it will just be deleted anyway after update.

I have SQL server 2008

(I know TIMESTAMP isn't a good field name, please excuse it, but that's what data dump was....)
I used following SQL kindly made up on another question for EXACT matches...
 
update HistorianData set [ddd] = D.[ddd]
  from [DataDump] D
 where HistorianData.[TIMESTAMP] = D.[DataDump]

Open in new window

0
rwallacej
Asked:
rwallacej
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this should do:
UPDATE t1
  SET fieldx = ( SELECT TOP 1 t2.Fieldx 
                 FROM table2 t2 
                 WHERE t2.TIMESTAMP >= DATEADD(minute, -1, t1.TIMESTAMP )
                   AND t2.TIMESTAMP <= DATEADD(minute, 1, t1.TIMESTAMP )
                )

Open in new window

0
 
rwallacejAuthor Commented:
thank-you - this works
regards,
rwallacej
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now