Identical records with little time difference [message #246929] |
Fri, 22 June 2007 09:08  |
sandman42
Messages: 14 Registered: June 2007
|
Junior Member |
|
|
Hi,
I have a table STAMPS:
ID NUMBER(10) NOT NULL
CARD VARCHAR2(10)
MYDATETIME DATE
STAMPER NUMBER(2)
where stamper are ticket stamp machines, card is a card serial # returned by the machine, and MYDATETIME is the date/time where the user stamps the card.
I need to identify if there are stamps of the same card from different machines at the same time, i.e. with a difference between mydatetime within 5 seconds.
How can I implement such a query?
Thanks
|
|
|
|
Re: Identical records with little time difference [message #246996 is a reply to message #246929] |
Fri, 22 June 2007 15:01   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Try the following
select card,mydatetime,stamper
from
(select card,
mydatetime,
stamper,
lag(stamper,1,null) over (partition by card order by mydatetime) last_stamp,
mydatetime - LAG(mydatetime,1,NULL) OVER (PARTITION BY card ORDER BY mydatetime) time_span
from stamps)
where nvl(time_span,1) <= (5 / (60*60*24))
and stamper <> nvl(last_stamp,stamper);
[Updated on: Fri, 22 June 2007 15:05] Report message to a moderator
|
|
|
|
|