Oracle – Identify Records with Identical Field but Different Timestamp

oracleoracle-sql-developer

I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on.

My current query uses this line in the SELECT statement to identify records that have a field that occurs elsewhere in the database: 

CASE SIGN(COUNT(*) - 2) WHEN -1 then 0 else 1 END AS "REPEAT_OFFENDER_BIT"

So basically it says "if there are multiple records with this field (which is determined by my GROUP BY clause), put a one in the REPEAT_OFFENDER_BIT column, else put a zero.

I also need to make sure that the timestamps associated with these records it finds as having a certain common field are not the same. Only then would the record get a one rather than a zero.

Let's say my table columns are these:

fname | lname | ssn | chargeddate

A row should get a one for REPEAT_OFFENDER_BIT only if there exists another record with the same SSN and a different charged date.

What are my options for achieving this? My SQL is quite primitive so I came here for help. Thank you!

Best Answer

Sample data:

create table offender
(
  fname varchar2(10),
  lname varchar2 (10),
  ssn number,
  chargeddate date
);

insert into offender values ('A', 'A', 1, date'2015-08-01');
insert into offender values ('B', 'B', 2, date'2015-08-02');
insert into offender values ('C', 'C', 3, date'2015-08-03');
insert into offender values ('D', 'D', 4, date'2015-08-04');
insert into offender values ('E', 'E', 1, date'2015-08-01');
insert into offender values ('F', 'F', 2, date'2015-08-05');
insert into offender values ('G', 'G', 3, date'2015-08-06');
insert into offender values ('H', 'H', 4, date'2015-08-04');
commit;

The query:

select
  fname,
  lname,
  ssn,
  chargeddate,
  decode(count(distinct chargeddate) over (partition by ssn), 1, 0, 1) as repeat_offender_bit
from
  offender;

FNAME      LNAME             SSN CHARGEDDATE         REPEAT_OFFENDER_BIT
---------- ---------- ---------- ------------------- -------------------
A          A                   1 2015-08-01 00:00:00                   0
E          E                   1 2015-08-01 00:00:00                   0
B          B                   2 2015-08-02 00:00:00                   1
F          F                   2 2015-08-05 00:00:00                   1
C          C                   3 2015-08-03 00:00:00                   1
G          G                   3 2015-08-06 00:00:00                   1
H          H                   4 2015-08-04 00:00:00                   0
D          D                   4 2015-08-04 00:00:00                   0
Related Question