SQL – Determining if Resources are in Use in 2 Hour Time Periods

10022010

February 10, 2010

A question appeared in the comp.databases.oracle.misc Usenet group a couple of years ago that caught my attention, and not just because the subject line read “need urgent help“. The original poster supplied the following request:

At this point, we might start thinking about what, if any, potential problems we might encounter. One of the challenges that we will face is working around the need to generate up to 12 rows (1 for each of the 2 hour long possible time periods) for each row in the source table. A second problem is how to handle logins that occur before midnight, with a corresponding logout that occurs after midnight. If it were known that there would be no time periods that cross midnight, we might try to build a solution like this using our test table:

The above just simplifies the input table into dates, login hour and logout hour.

Next, we need a way to generate 12 rows. We could just use an existing table, and specify that we want to return all rows where ROWNUM<=12, but we will use CONNECT BY LEVEL, which could result in greater CPU consumption, but would likely be more portable:

Now that we have the two simplified data sets, we just need to find where the two data sets intersect. First, let’s find those records where the numbers from the counter fall between the LOGIN_HOUR and the LOGOUT_HOUR:

By also allowing the LOGIN_HOUR to fall between the LOGIN_COUNTER and LOGOUT_COUNTER, or the LOGOUT_HOUR to fall between the LOGIN_COUNTER and LOGOUT_COUNTER (with a slight adjustment), we pick up the missing row. Now, it is a simple matter to find the total number in each time period by sliding the above into an inline view:

The above SQL statement is likely not the only solution to the problem. Let’s take another look at the problem. What if there is a need for the time intervals to cross midnight. We need to make a couple of adjustments. First, let’s add another row to the table for variety:

We then combine the above with a simple counter that counts from 1 up to 12, only joining those rows from the counter that are less than or equal to the calculated number of intervals. By adding the number of hours determined by the counter to the adjusted LOGIN_HOUR_A, values we obtain the time intervals:

I don’t think that anyone mentioned it in the Usenet thread, but Ed Prochak’s suggestion was correct. It does not matter much if someone is trying to solve an algebra problem (or even manually attempting to solve a long division problem), a performance tuning problem, or a SQL related problem – what is required is a logical, step-by-step approach to tackling the problem, with each step moving closer to the end result.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: