Posted by Erland Sommarskog on 04/06/06 00:42

Jeff Kish (jeff.kish@mro.com) writes:
> I have two tables
> one with app, msg, time
> (varchar,datetime,varchar)
>
> app1 start 2006-04-03 13:33:36.000
> app1 stuff 2006-04-03 13:33:36.000
> app1 end 2006-04-03 13:33:36.000
>
> and another with dr watson crash info
> (varchar, datetime)
> app1 2006-04-03 13:33:36.000
> app2 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app3 2006-04-03 13:33:36.000
>
>
> I'm trying to make a query that will allow
> me to see what entries in the first table
> occurred wtihin, say, a minute, or maybe 40
> seconds of any of the entries in the second
> table.
>
> I want all the entries in the second table to
> be present, so I know it has to be some sort
> of join, probably an outer join.

There is a standard recommendation for this sort of posts, and that is
that you post:

o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it very easy to copy and paste into a query tool to develop a
tested solution.

With the information you have given, I can only give a non-tested solution,
which is also is just a guess of what you are looking for.

SELECT w.app1, w.datetimecol, o.event, o.datetimecol
FROM drwatson w
LEFT JOIN othertable o
ON w.app = o.app
AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]