Re: datetime diff query syntax

Jeff Kish (jeff.kish@xxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Very slow query
    ... with lots if information in Books Online, Books, Courses, the Net etc. ... While testing the query in QA, use the "Show Estimated Query Plan" feature. ... This is what I suggest you also pick up using Profiler. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... What you said was what I first planned to say, but then I read the Books Online which implies that the batch is indeed reused, so I held back from saying that. ... the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ...
    (microsoft.public.sqlserver.tools)
  • Re: Many to one Select
    ... It still possible to define a query that has maximum of columns needed, ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: timezones in select statement
    ... > end, in the sense that I don't want it to be pulled back in the query, ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: UPDATE query gives Incorrect Syntax error
    ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... No - no triggers on the table. ... Is it possible that a previous query has somehow become trapped and is ...
    (comp.databases.ms-sqlserver)