-
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.
-
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
-
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
-
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.
-
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.
-
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.
-
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.
-
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
-
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!
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|