|
|
Posted by Jeff Kish on 04/06/06 08:28
On Wed, 5 Apr 2006 21:42:37 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:
>Jeff Kish (jeff.kish@mro.com) writes:
>> I have two tables
<snip>
>
>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.
>
I understand. I'll remember this in the future.
>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
and also the other message reply said...
>There are a couple of different ways to do this. This one may not be
>the best. It's just the first thing that popped into my mind. Hope it
>helps. Your sample data was all the same timestamp. I created sample
Yes, I was in a hurry and was careless. Normally the data is very
much just as you thought below.
>data where a crash occurs within one minute of an entry for app1 and
>another crash within a minute of an entry for app3. App2 is output in
>the results becuase you specifically requested that.
>
>Christopher Secord
>
>create table AppMessage (
> App char(4),
> MsgType char(5),
> MsgDate datetime
>)
>create table DRWatsonCrash (
> App char(4),
> CrashDate datetime
>)
>
>insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
>insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
>insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
>insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
>insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
>insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
>insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
>insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')
>
>insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
>insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
>insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')
>
>
>select AppMessage.App as Application, MsgType, MsgDate
>from AppMessage, DrWatsonCrash
>where AppMessage.App = DRWatsonCrash.App
>and CrashDate between dateadd(minute,-1,MsgDate) and
>dateadd(minute,1,MsgDate)
>union all
>select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
>from DRWatsonCrash
>order by Application, MsgDate
Thanks much. I'll try both solutions.
I appreciate the feedback.
Jeff
Navigation:
[Reply to this message]
|