Posted by christopher.secord@gmail.com on 04/06/06 00:35

Jeff Kish wrote:
> Hi.
> I'm trying but not getting correct results.
(snip)

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
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

[Back to original message]