SQL Question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: SQL Question

  1. #1
    Join Date
    Jul 2001
    Posts
    59

    Question

    I'm pretty new to SQL and I'm trying to write a query which will find two records that are within seconds of each other.

    This is a database of infomation gathered from scanners. I'm trying to find an instance where somone with the same login approved a substitute part in two different places at the same time. (In order to show that he has given out his supervisor password)

    It goes something like this:

    SELECT worker_login, datetime, supervisor_login
    FROM history
    WHERE supervisor_login = 'OFFENDER'
    AND datetime ?????????????????????
    ORDER by datetime;

    Can anyone think of an easy way or function that will check to see if two datetime entries were within seconds of each other?

    Obviously this will invole a conversion function like to_char and to_date, with some calculation, I'm just not sure how to do it.

  2. #2
    Join Date
    Jul 2001
    Posts
    59

    ALSO

    Also, why can't I use a function with an alias...

    For example:

    SELECT TO_CHAR(datetime, 'HH24:MI:SS DD-MON-YY') date

    I'm trying to convert the "datetime" column to a char so I can format it and then give it the alias "date"

    SQL Plus won't let me do this

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

    Hiidey ho shagy.

    You could use the 'BETWEEN' clause.

    SELECT worker_login, datetime, supervisor_login
    FROM history
    WHERE supervisor_login = 'OFFENDER'
    AND datetime BETWEEN 'date format' AND 'date format';

    Know what I mean?

    MH

  4. #4
    Join Date
    Jul 2001
    Posts
    59

    Re: ALSO

    Originally posted by shagymoe
    Also, why can't I use a function with an alias...

    For example:

    SELECT TO_CHAR(datetime, 'HH24:MI:SS DD-MON-YY') date

    I'm trying to convert the "datetime" column to a char so I can format it and then give it the alias "date"

    SQL Plus won't let me do this
    Use quotation marks on date, knucklehead.

  5. #5
    Join Date
    Jul 2001
    Posts
    59
    Originally posted by Mr.Hanky
    Hiidey ho shagy.

    You could use the 'BETWEEN' clause.

    SELECT worker_login, datetime, supervisor_login
    FROM history
    WHERE supervisor_login = 'OFFENDER'
    AND datetime BETWEEN 'date format' AND 'date format';

    Know what I mean?

    MH
    I know what you mean, but I don't know the exact times that I'm looking for.

    For example:

    He could have scanned one at 4:30:01 and 4:30:03 or it could have been 6:14:44 and 6:14:45 or any other hour:minute:second....who knows.



  6. #6
    Join Date
    Jul 2001
    Posts
    59
    One thing that I thougt to do was to first convert the datetime column to a char using

    TO_CHAR(datetime, 'HH24MISS')

    So, this will give me a number like 163001 for 4 thirty and 1 second. This works, I tried it.

    Then, convert this CHAR to a NUMBER and do a calculation.

  7. #7
    Join Date
    Jul 2001
    Posts
    59
    This doesn't work:

    SELECT to_char(datetime, 'HH24MISS')
    FROM history
    WHERE login = 'OFFENDER'
    AND TO_NUMBER(datetime, '999999') = 163000;

    This doesn't compare anything, only a test to see if I can change it to a number and then do a calcualtion (still to be figured out) later.

  8. #8
    Join Date
    Feb 2000
    Posts
    175
    How about something like....

    select a.worker_login, a.datetime, b.supervisor_login, b.datetime
    FROM history a, history b
    WHERE b.supervisor_login = 'OFFENDER'
    AND (a.datetime >= b.datetime-(1/1440) and a.datetime <=b.datetime)
    ORDER by datetime;

    This should print (I think!!!) the worker, supervisor and the login times where the worker logged in upto 1 minute before the supervisor

  9. #9
    Join Date
    Jul 2001
    Posts
    59
    Originally posted by moff
    How about something like....

    select a.worker_login, a.datetime, b.supervisor_login, b.datetime
    FROM history a, history b
    WHERE b.supervisor_login = 'OFFENDER'
    AND (a.datetime >= b.datetime-(1/1440) and a.datetime <=b.datetime)
    ORDER by datetime;

    This should print (I think!!!) the worker, supervisor and the login times where the worker logged in upto 1 minute before the supervisor
    I'll give that a shot and let you know. Thanks!


  10. #10
    Join Date
    Jul 2001
    Posts
    59
    Originally posted by shagymoe
    Originally posted by moff
    How about something like....

    select a.worker_login, a.datetime, b.supervisor_login, b.datetime
    FROM history a, history b
    WHERE b.supervisor_login = 'OFFENDER'
    AND (a.datetime >= b.datetime-(1/1440) and a.datetime <=b.datetime)
    ORDER by datetime;

    This should print (I think!!!) the worker, supervisor and the login times where the worker logged in upto 1 minute before the supervisor
    I'll give that a shot and let you know. Thanks!

    Well, this managed to lock up SQL Plus pretty good...but thats all. :(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width