CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008t-sql
cowsay
cowsay

Reputation: 1332

Joining tables based on a datetime, but the datetime value can vary within 5 minutes

I'm working with some rather poorly designed tables here, and changing the structure is more or less out of the question. So with that said, here's my question.

I need to join TableB.Date on TableA.Date, with a margin of about 5 minutes. In other words, the join can't rely on a perfect match.

So if the date in TableA is 2011-12-01 10:00:00.000, the join would match a record in TableB where the date is, 2011-12-01 10:03:00.000 or 2011-12-01 09:59:00.000 or 2011-12-01 10:04:35.000

I came up with a little trick to allow a margin of difference of about 1 minute by casting the datetime as smalldatetime, which drops the second and millisecond from the datetime value. It works quite well, but I need a larger margin than that.

LEFT JOIN TableB ON CAST(TableA.SomeDate AS smalldatetime) = CAST(TableB.SomeDate AS smalldatetime)

The date column in TableB is the only linkage between data in the two tables, so I cannot filter the results within the WHERE clause. I realize this can potentially produce some inaccurate results, but because of how and when the data is stored, I shouldn't run into any issues.

Any suggestions?

Upvotes: 4

Views: 10648

Answers (3)

Davos
Davos

Reputation: 5415

The problem with this is that you have to define the 5 minute margin more rigidly for SQL to work well. If you want to be fuzzier then SQL is the wrong language.

What if 2 results on tableA are within 5 minutes of each other? Do they both join to the same record on tableB? You then get duplicated records.

What you want to do is bin your datetimes into 5 minute buckets (or whatever you choose) using a round down/up plan. A pre-made lookup table is always a quick performing solution, that table would contain an index column of every minute of every day and another column with your agreed 5 minute buckets. This intermediate table could be used to join tableA to tableB.

i.e. The LookupTable (excuse the formatting!)

LookupTime __________ BucketTime

2013-01-01 14:56 _____ 2013-01-01 14:55

2013-01-01 14:57_____ 2013-01-01 14:55

2013-01-01 14:58_____ 2013-01-01 15:00

2013-01-01 14:59_____ 2013-01-01 15:00

You would join the tableA date to the LookupTime above. You would do a separate join to a second copy of the LookupTime table for tableB, Then join the two copies of the lookup table to each other on the BucketTime Field.

The tricky part is agreeing on the rounding you will use. I recommend "Round To Even" which smooths out the rounding up bias that comes with rounding digits 1-4 down and 5-9 up.

Upvotes: 1

gbn
gbn

Reputation: 432361

Try DATEDIFF to look for less then 300 seconds (minute boundary is zero seconds which will give incorrect results). For more accuracy it matter, use milliseconds < 300000

TableA
LEFT JOIN 
TableB ON ABS(DATEDIFF(second, TableA.SomeDate, TableB.SomeDate)) < 300

Upvotes: 7

MatthewMartin
MatthewMartin

Reputation: 33153

Join formatted dates. Format them so that they either round to the nearest 5 minutes or round to the relevant time span. It will probably require a user defined function.

Upvotes: 0

Related Questions