datetime diff query syntax

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jeff Kish
    #1

    datetime diff query syntax

    Hi.
    I'm trying but not getting correct results.

    I have two tables
    one with app, msg, time
    (varchar,dateti me,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
    app1 start 2006-04-03 13:33:36.000
    app2 start 2006-04-03 13:33:36.000
    app2 stuff 2006-04-03 13:33:36.000
    app2 end 2006-04-03 13:33:36.000
    app2 start 2006-04-03 13:33:36.000
    app3 start 2006-04-03 13:33:36.000
    app2 end 2006-04-03 13:33:36.000
    app2 start 2006-04-03 13:33:36.000
    app2 end 2006-04-03 13:33:36.000
    app2 start 2006-04-03 13:33:36.000
    app2 end 2006-04-03 13:33:36.000
    app3 end 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.

    my syntax is giving me bad results, probably
    because I'm just out of practice.

    can someone tell me how to put a query together
    so I see the data I'm looking for?
    Thanks
    Jeff


    Jeff Kish
    Tags: None
    • christopher.secord@gmail.com
      #2
      Re: datetime diff query syntax

      Jeff Kish wrote:[color=blue]
      > Hi.
      > I'm trying but not getting correct results.[/color]
      (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.A pp
      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

        Comment

        • Erland Sommarskog
          #3
          Re: datetime diff query syntax

          Jeff Kish (jeff.kish@mro. com) writes:[color=blue]
          > I have two tables
          > one with app, msg, time
          > (varchar,dateti me,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.[/color]

          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@sommarsk og.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

            Comment

            • Jeff Kish
              #4
              Re: datetime diff query syntax

              On Wed, 5 Apr 2006 21:42:37 +0000 (UTC), Erland Sommarskog
              <esquel@sommars kog.se> wrote:
              [color=blue]
              >Jeff Kish (jeff.kish@mro. com) writes:[color=green]
              >> I have two tables[/color][/color]
              <snip>[color=blue]
              >
              >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.
              >[/color]
              I understand. I'll remember this in the future.[color=blue]
              >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[/color]

              and also the other message reply said...
              [color=blue]
              >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[/color]
              Yes, I was in a hurry and was careless. Normally the data is very
              much just as you thought below.[color=blue]
              >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.A pp
              >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[/color]

              Thanks much. I'll try both solutions.
              I appreciate the feedback.
              Jeff

                Comment

                Previous template Next
                Working...