SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to do count based on rolling 30 mins

976439Sep 23 2016 — edited Sep 26 2016

Hi, I was looking to find out how i can do a count based on date and timestamps. I currently have the following data and I would like to do a count on the different timestamps but for a rolling 30 minute period.

Agent  | Date_1
Joe Bloggs | 15-jun-16 13:09:00
Joe Bloggs | 15-jun-16 13:09:00
Joe Bloggs | 15-jun-16 13:29:00
Joe Bloggs | 15-jun-16 13:55:00
Joe Bloggs | 16-jun-16 11:11:00

I have written the following query:

SELECT
TRUNC(DATE_1) AS DATE_1
ROW_NUMBER() OVER (PARTITION BY AGENT, TRUNC(DATE_1) ORDER BY DATE_1) AS COUNT_1
FROM TABLE_1

and this gives me:

Agent | Date_1 | Count_1
Joe Bloggs | 15-jun-16 | 4
Joe Bloggs | 16-jun-16 | 1

but what i would like is that the first 2 rows have the same date/time so this counts 1 and then the 3rd row is at 13:29 so within 20 mins so count stays 1, but the fourth row shows a value of 13:55 which is 30 mins after the first row so this will now count 2.

Results should be:

Agent | Date_1 | Count_1
Joe Bloggs | 15-jun-16 | 2
Joe Bloggs | 16-jun-16 | 1

Would appreciate if somebody could please advise on how i can get round this issue.

Thanks in advance.

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 24 2016

Added on Sep 23 2016

25 comments

4,127 views