Home » SQL & PL/SQL » SQL & PL/SQL » Identical records with little time difference
Identical records with little time difference [message #246929] Fri, 22 June 2007 09:08 Go to next message
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 #246930 is a reply to message #246929] Fri, 22 June 2007 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The simplest way (maybe not the most efficient) is (assuming id is the PK):
select distinct *
from stamps a, stamps b
where a.card = b.card
  and a.id != b.id
  and a.stamper != b.stamper
  and abs(a.mydatetime-b.mydatetime) <= 5/86400
/

Regards
Michel
Re: Identical records with little time difference [message #246996 is a reply to message #246929] Fri, 22 June 2007 15:01 Go to previous messageGo to next message
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

Re: Identical records with little time difference [message #247351 is a reply to message #246929] Mon, 25 June 2007 11:41 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Did either of these work for you? A little feedback would be appreciated.
Re: Identical records with little time difference [message #255042 is a reply to message #247351] Mon, 30 July 2007 08:40 Go to previous message
sandman42
Messages: 14
Registered: June 2007
Junior Member
Yes, it did.

Excuse me and thanks for your help.
Previous Topic: out param storing the previous value of in param
Next Topic: connection problem
Goto Forum:
  


Current Time: Wed Feb 19 01:41:13 CST 2025