Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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