CODEWITHSUNDEEP
javasqloracle-database
Roberto Linares
Roberto Linares

Reputation: 2225

Selecting Maximum number of concurrent records based on timeStart and timeEnd values

I have an SQL Table in an Oracle server containing values of the start and end time of an event and want to know what is the maximum number of concurrent events happening in the same second via an SQL Query if possible.

My table structure is:

EventID    StartTime   EndTime
===================================
12345      10:25:50    10:25:59
12346      10:25:51    10:26:00
12347      10:25:55    10:26:10

I have tried making a query selecting the hour, minute and second as separate fields and group the records by them but since I have time ranges I don't know how to check when they overlap (since they are being executed concurrently if they have overlapping time).

If it is not possible as a query, can you recommend me an efficient way to calculate this data in Java? I can think of a way by looping the ResultSet but don't know exactly how to permute the overlapping events.

Upvotes: 0

Views: 203

Answers (2)

chirag
chirag

Reputation: 1

package test3;

import java.util.Scanner;

public class maximumnumbercheck {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
int a,b;

        System.out.println("Enter A Value");
Scanner max=new Scanner(System.in);
a=max.nextInt();
System.out.println("Enter B Value");
b=max.nextInt();


max.close();


String msg=(a>=b)? "  A is Maximum NUmber " :"  b Is Maximum Number";
System.out.println("msg  "+msg);
    }

}

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Create a list of all the times with an increment -- +1 when an event starts and -1 when an event ends.

Then use a cumulative sum to get the total active at any time and pull the maximum value:

with times as (
      select starttime as t, +1 as inc
      from structure
      union all
      select endtime, -1
      from structure
     )
select t.*
from (select t, sum(inc) over (order by t) as actives
      from (select t, sum(inc) as inc
            from times
            group by t
           ) t
      order by actives desc
    ) t
where rownum = 1;

The innermost subquery (with the group by) is just to handle things that happen at exactly the same time.

Upvotes: 2

Related Questions