0

I have only gone so far:

select timestamp, trans, Count(trans)  
From(
    Select to_char(CREATED_TIMESTAMP) as timestamp,SOURCE_MSISDN||DEST_MSISDN||AMOUNT as trans
    From ADMDBMC.TRANSACTION_CASH
    WHERE TO_date(CREATED_TIMESTAMP) > = '1-sep-2014' AND TO_date(CREATED_TIMESTAMP) < '2-sep-2014'
    and STATUS_DESCRIPTION='SUCCESS'
    ) 

group by timestamp,trans
Having count(trans)>1
order by count(trans) desc
Erik Kaplun
  • 37,128
  • 15
  • 99
  • 111
Kax
  • 3
  • 2
  • Query is ok whats its result? – Zeshan Khan Sep 10 '14 at 11:03
  • Why are you applying the `to_date()` function on a column that is (apparently) already a `date` (or `timestamp`). All that you are doing is to convert the `date` to a `varchar` and then convert it back to a `date`. That's totally useless. –  Sep 10 '14 at 11:15
  • I will correct that @Zeshan I want the query to only give me results of transaction less than 10 minutes apart. – Kax Sep 10 '14 at 11:21
  • did you mean from 1-sep-2014 00:00 to 1-sep-2014 00:10? @Kax – Zeshan Khan Sep 10 '14 at 11:30
  • You want to include any transaction in your result provided you can also find another transaction - no more that 10 minutes apart? – Jens Krogsboell Sep 10 '14 at 11:31

3 Answers3

0

I think you can achieve that by truncating your timestamp on the nearest multiple of 10 minutes: you could replace to_char(CREATED_TIMESTAMP) as timestamp with regexp_replace(to_char(CREATED_TIMESTAMP, 'dd-Mon-yyyy hh24:mi'), '.$', '0') and I think your group by would then be ok.

EDIT : the previous solution was only working if the 2 transactions were part of the same multiple of 10 minutes. Here is a better one:

Select *
From
(
    Select CREATED_TIMESTAMP,
           SOURCE_MSISDN||DEST_MSISDN||AMOUNT as trans,
           lag(CREATED_TIMESTAMP, 1, null) over (partition by SOURCE_MSISDN||DEST_MSISDN||AMOUNT order by CREATED_TIMESTAMP) as PREVIOUS_TIMESTAMP
    From ADMDBMC.TRANSACTION_CASH
    Where TO_date(CREATED_TIMESTAMP) >= '1-sep-2014'
      And TO_date(CREATED_TIMESTAMP) < '2-sep-2014'
      And STATUS_DESCRIPTION='SUCCESS'
) 
where CREATED_TIMESTAMP <= PREVIOUS_TIMESTAMP + INTERVAL '10' MINUTE

The lag analytical function allows to get for every row the timestamp of preceeding row of the same group identified by the concatenation of SOURCE_MSISDN / DEST_MSISDN / AMOUNT. Then once we have this, we just have to check the difference.

Emmanuel
  • 13,935
  • 12
  • 50
  • 72
  • I want to check the time in between time stamps.. if the time is less than 10 min then return the result.. so for example if first time time stamp is 1:03 AM and next time stamp is 1:12 AM then then that transaction should appear in the resuts – Kax Sep 10 '14 at 12:05
0

Emmanuels way would return false negative for example if one transaction occured at 11:49 and second at 11:52

You could use LEAD or LAG aggregate function with window option to get timestamp of a older row then current and calculate difference between two timestamps or COUNT(*) with window, for example:

select * FROM ( Select to_char(CREATED_TIMESTAMP) as timestamp,SOURCE_MSISDN||DEST_MSISDN||AMOUNT as trans ,COUNT(*) OVER (PARTITION BY SOURCE_MSISDN||DEST_MSISDN||AMOUNT ORDER BY CREATED_TIMESTAMP RANGE NUMTODSINTERVAL(600,'SECOND') PRECEDING) L_OF_TRANS From ADMDBMC.TRANSACTION_CASH WHERE TO_date(CREATED_TIMESTAMP) > = '1-sep-2014' AND TO_date(CREATED_TIMESTAMP) < '2-sep-2014' and STATUS_DESCRIPTION='SUCCESS' ) WHERE L_OF_TRANS > 1

Community
  • 1
  • 1
Looneytoons
  • 36
  • 1
0

You should try using to_dsinterval function. Subtracting timestamps gives intervals, so you can compare timestamps using to_dsinterval. For example:

select *
from my_table x
where x.timestamp1 - x.timestamp2 < to_dsinterval ('00 00:10:00');

In this example, timestamp1 would be the more recent timestamp.

tbone
  • 15,107
  • 3
  • 33
  • 40