I have following sample data in different tables
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
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]