Avatar of jflodin
jflodinFlag for Czechia

asked on 

Closest time

I have two tables

Table Messages:

prim_Key           Truck           Message                  MessageTime           Country     City
1                        ABC999       'some message'       21.7.2008 16:01      ?               ?
2                        ABC123       'another message'   21.7.2008 16:25       ?               ?        

Table Positions:

prim_key          Truck          MeasuredTime       Country        City
1                      ABC999      21.7.2008 12:51    Germany       Kiel
2                      ABC999      21.7.2008 14:51    Germany       Hamburg
3                      ABC999      21.7.2008 19:20    Germany       Berlin
4                      ABC123      21.7.2008 16:20    France          Paris

We have our trucks going around whole Europe. Several times a day we exchange short electronical messages between our Dispatching and each Truck. Moreover we have a GPS system which every 2nd minute writes each Truck's position to a table. Basically I want to write an insert trigger on the Message table which updates the  Message table with the Country and City from the Positions table based on the record in the Positions table which have the closest MeasuredTime to MessageTime in the Messages table.

In this case record nr 1 in the Messages table should be updated with Germany as Country and Hamburg as City.

Thanks in advance for your help.

Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

question 1: sql server 2005 and higer, or sql 2000 and lower?
question 2: why save the country+city, wouldn't it be more efficient to just store the positions.prim_key value in a single column of messages table?
question 3: the messages get inserted "later" than the actual "MessageTime"?
Avatar of jflodin
Flag of Czechia image


1. SQL Server 2005
2. Yes, you are right about that. But in this case the Positions table are filled with around 200.000 records each day. So I will have some kind of delete query which will remove records which are lets say older than 2 days. So then the prim_key will have no sence since it will not be found in the Positions table.
3. Yes, the message gets inserted later.

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of jflodin
Flag of Czechia image


Thanks a lot.
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo