Solved

SQL update matching first record with closest time

Posted on 2009-05-15
2
510 Views
Last Modified: 2012-06-27
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
Comment
Question by:rwallacej
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24394210
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
 

Author Closing Comment

by:rwallacej
ID: 31581821
thank-you - this works
regards,
rwallacej
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now