| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL: Duration Question
dave.howland_at_gmail.com wrote:
> Hello everyone:
>
> I have a db table that contains three pieces of information ( let's say
> I was tracking car trips), a start time(date), an end time(date) and a
> duration(int) What I want to know (two things) (a) in every hour of
> every day, is there a way that I could tell the maximum number of
> concurrent cars on the road? (b) what is the total number of cars on
> the road in each hour?
>
> Can this be done is SQL?
If you plan to deploy on multiple database platforms, you will have an easier time accomplishing this if you retrieve raw data from the database, and use application code to perform the analysis. Using such a method will cause poor performance, but it will make it easier to transport the code. The book "SQL for Smarties" may have some ideas for you - I have that book at work in the office, so I can't check right now to determine if there is a specific example in the book that is very similar to what you are trying to accomplish.
A very brief example, using only SQL to find the number of cars on the
road at a given hour, using a sample table from my database:
SELECT
RESOURCE_ID,
CLOCK_IN,
CLOCK_OUT
FROM
LABOR_TICKET
WHERE
SHIFT_DATE='02-DEC-2006'
AND INDIRECT_ID IS NULL
ORDER BY
RESOURCE_ID;
RESOURCE_ID CLOCK_IN CLOCK_OUT
=============== ==================== ==================== 101 02-DEC-2006 04:09:15 02-DEC-2006 06:11:02 101 02-DEC-2006 06:11:26 02-DEC-2006 07:09:02 102 02-DEC-2006 05:34:16 02-DEC-2006 10:58:16 102 02-DEC-2006 11:06:29 02-DEC-2006 12:13:17 102 02-DEC-2006 12:13:17 02-DEC-2006 14:15:17 106 02-DEC-2006 04:35:03 02-DEC-2006 07:49:28 106 02-DEC-2006 07:49:28 02-DEC-2006 13:58:01 106 02-DEC-2006 16:17:55 03-DEC-2006 03:58:03 107 02-DEC-2006 12:12:25 02-DEC-2006 14:05:19 341 02-DEC-2006 02:00:00 02-DEC-2006 02:31:30 341 02-DEC-2006 02:31:30 02-DEC-2006 06:46:48 341 02-DEC-2006 06:46:48 02-DEC-2006 10:39:56 341 02-DEC-2006 10:39:56 02-DEC-2006 12:00:56 362 02-DEC-2006 03:58:40 02-DEC-2006 12:23:40 362 02-DEC-2006 11:55:27 02-DEC-2006 12:21:36 362 02-DEC-2006 12:21:36 02-DEC-2006 20:01:25 436 02-DEC-2006 06:00:00 02-DEC-2006 12:00:46 436 02-DEC-2006 12:00:00 02-DEC-2006 17:16:34 436 02-DEC-2006 17:16:34 02-DEC-2006 20:01:12 54 02-DEC-2006 05:08:44 02-DEC-2006 10:47:16 54 02-DEC-2006 10:47:16 02-DEC-2006 12:23:22 54 02-DEC-2006 15:38:19 03-DEC-2006 01:54:34 54 03-DEC-2006 01:54:34 03-DEC-2006 03:30:00 58 02-DEC-2006 07:02:37 02-DEC-2006 11:04:37 58 02-DEC-2006 11:04:37 02-DEC-2006 12:06:43 58 02-DEC-2006 12:06:43 02-DEC-2006 15:07:52 58 02-DEC-2006 15:58:00 02-DEC-2006 19:26:16 58 02-DEC-2006 19:26:16 02-DEC-2006 21:10:11 58 02-DEC-2006 21:10:11 03-DEC-2006 03:14:35 58 03-DEC-2006 03:14:35 03-DEC-2006 04:00:17 59 02-DEC-2006 04:11:23 02-DEC-2006 13:56:11 62 02-DEC-2006 06:36:21 02-DEC-2006 15:30:28 63 02-DEC-2006 15:40:31 02-DEC-2006 20:37:04 63 02-DEC-2006 20:37:04 03-DEC-2006 01:19:43 63 03-DEC-2006 01:19:43 03-DEC-2006 03:50:01 67 02-DEC-2006 07:47:57 02-DEC-2006 14:55:41 67 02-DEC-2006 15:54:08 02-DEC-2006 16:34:31 67 02-DEC-2006 16:34:31 03-DEC-2006 01:37:17 67 03-DEC-2006 01:37:17 03-DEC-2006 04:01:23...
To make the equation work as you would like, if the resource 101 starts
at 04:09:15 and finishes at 06:11:02, you want it to count 4:00, 5:00,
6:00, we will need to make some adjustments to the values in the
CLOCK_IN and CLOCK_OUT columns:
SELECT
RESOURCE_ID,
CLOCK_IN,
CLOCK_OUT,
TRUNC(CLOCK_IN,'HH') FIXED_CLOCK_IN,
DECODE(SIGN(TRUNC(CLOCK_OUT,'MI')-TRUNC(CLOCK_OUT,'HH')),0,TRUNC(CLOCK_OUT,'MI'),TRUNC(CLOCK_OUT,'HH')+1/24)
FIXED_CLOCK_OUT,
(DECODE(SIGN(TRUNC(CLOCK_OUT,'MI')-TRUNC(CLOCK_OUT,'HH')),0,TRUNC(CLOCK_OUT,'MI'),TRUNC(CLOCK_OUT,'HH')+1/24)-TRUNC(CLOCK_IN,'HH'))*24
HOURS
FROM
LABOR_TICKET
WHERE
SHIFT_DATE='02-DEC-2006'
AND INDIRECT_ID IS NULL
ORDER BY
RESOURCE_ID;
In the above, we truncate the CLOCK_IN time to the starting hour of the
CLOCK_IN: 04:09:15 becomes 04:00:00. Handling the CLOCK_OUT is a bit
more difficult, since 04:00:17 should not become 05:00:00, while
04:01:23 should become 05:00:00 since it is at least one full minute
after 04:00:00. By truncating the CLOCK_OUT column to the minute, and
again to the hour, if the values are the same, then we select to
truncate to the minute, otherwise we truncate to the hour and add 1/24,
which is the equivalent of one hour. The final column shows the number
of hours that you wanted to count - if the resource 101 starts at
04:09:15 and finishes at 06:11:02, you want it to count 4:00, 5:00,
6:00, which is 3 hours:
RESOURCE_ID FIXED_CLOCK_IN FIXED_CLOCK_OUT HOURS
=============== ==================== ==================== ========== 101 02-DEC-2006 04:00:00 02-DEC-2006 07:00:00 3 101 02-DEC-2006 06:00:00 02-DEC-2006 08:00:00 2 102 02-DEC-2006 05:00:00 02-DEC-2006 11:00:00 6 102 02-DEC-2006 11:00:00 02-DEC-2006 13:00:00 2 102 02-DEC-2006 12:00:00 02-DEC-2006 15:00:00 3 106 02-DEC-2006 04:00:00 02-DEC-2006 08:00:00 4 106 02-DEC-2006 07:00:00 02-DEC-2006 14:00:00 7 106 02-DEC-2006 16:00:00 03-DEC-2006 04:00:00 12 107 02-DEC-2006 12:00:00 02-DEC-2006 15:00:00 3 341 02-DEC-2006 02:00:00 02-DEC-2006 03:00:00 1 341 02-DEC-2006 02:00:00 02-DEC-2006 07:00:00 5 341 02-DEC-2006 06:00:00 02-DEC-2006 11:00:00 5 341 02-DEC-2006 10:00:00 02-DEC-2006 12:00:00 2 362 02-DEC-2006 03:00:00 02-DEC-2006 13:00:00 10 362 02-DEC-2006 11:00:00 02-DEC-2006 13:00:00 2 362 02-DEC-2006 12:00:00 02-DEC-2006 21:00:00 9 436 02-DEC-2006 06:00:00 02-DEC-2006 12:00:00 6 436 02-DEC-2006 12:00:00 02-DEC-2006 18:00:00 6 436 02-DEC-2006 17:00:00 02-DEC-2006 21:00:00 4 54 02-DEC-2006 05:00:00 02-DEC-2006 11:00:00 6 54 02-DEC-2006 10:00:00 02-DEC-2006 13:00:00 3 54 02-DEC-2006 15:00:00 03-DEC-2006 02:00:00 11 54 03-DEC-2006 01:00:00 03-DEC-2006 04:00:00 3 58 02-DEC-2006 07:00:00 02-DEC-2006 12:00:00 5 58 02-DEC-2006 11:00:00 02-DEC-2006 13:00:00 2 58 02-DEC-2006 12:00:00 02-DEC-2006 16:00:00 4 58 02-DEC-2006 15:00:00 02-DEC-2006 20:00:00 5 58 02-DEC-2006 19:00:00 02-DEC-2006 22:00:00 3 58 02-DEC-2006 21:00:00 03-DEC-2006 04:00:00 7 58 03-DEC-2006 03:00:00 03-DEC-2006 04:00:00 1 59 02-DEC-2006 04:00:00 02-DEC-2006 14:00:00 10 62 02-DEC-2006 06:00:00 02-DEC-2006 16:00:00 10 63 02-DEC-2006 15:00:00 02-DEC-2006 21:00:00 6 63 02-DEC-2006 20:00:00 03-DEC-2006 02:00:00 6 63 03-DEC-2006 01:00:00 03-DEC-2006 04:00:00 3 67 02-DEC-2006 07:00:00 02-DEC-2006 15:00:00 8 67 02-DEC-2006 15:00:00 02-DEC-2006 17:00:00 2 67 02-DEC-2006 16:00:00 03-DEC-2006 02:00:00 10 67 03-DEC-2006 01:00:00 03-DEC-2006 05:00:00 4...
You may notice now, that we have introduced another problem, the FIXED_CLOCK_IN and FIXED_CLOCK_OUT times of the same resource may now overlap with another row for the same resource, and we can't simply add the HOURS column to determine the number of hours a particular resource was in use.
What if, I wanted to find the number of these resources that were in
use during the 6AM time period (6:00 AM to 6:59:59 AM)? I could slide
the above into an inline view and then select the distinct RESOURCE_ID
from the results:
SELECT DISTINCT
RESOURCE_ID
FROM
(SELECT
RESOURCE_ID,
CLOCK_IN,
CLOCK_OUT,
TRUNC(CLOCK_IN,'HH') FIXED_CLOCK_IN,
DECODE(SIGN(TRUNC(CLOCK_OUT,'MI')-TRUNC(CLOCK_OUT,'HH')),0,TRUNC(CLOCK_OUT,'MI'),TRUNC(CLOCK_OUT,'HH')+1/24)
FIXED_CLOCK_OUT,
(DECODE(SIGN(TRUNC(CLOCK_OUT,'MI')-TRUNC(CLOCK_OUT,'HH')),0,TRUNC(CLOCK_OUT,'MI'),TRUNC(CLOCK_OUT,'HH')+1/24)-TRUNC(CLOCK_IN,'HH'))*24
HOURS
FROM
LABOR_TICKET
WHERE
SHIFT_DATE='02-DEC-2006'
AND INDIRECT_ID IS NULL)
WHERE
TO_DATE('12/02/2006 06:00','MM/DD/YYYY HH24:MI') BETWEEN
FIXED_CLOCK_IN AND FIXED_CLOCK_OUT
ORDER BY
RESOURCE_ID;
RESOURCE_ID
29 ROWS SELECTED
A slight modification:
SELECT
COUNT(DISTINCT RESOURCE_ID) IN_USE
FROM
(SELECT
RESOURCE_ID,
CLOCK_IN,
CLOCK_OUT,
TRUNC(CLOCK_IN,'HH') FIXED_CLOCK_IN,
DECODE(SIGN(TRUNC(CLOCK_OUT,'MI')-TRUNC(CLOCK_OUT,'HH')),0,TRUNC(CLOCK_OUT,'MI'),TRUNC(CLOCK_OUT,'HH')+1/24)
FIXED_CLOCK_OUT,
(DECODE(SIGN(TRUNC(CLOCK_OUT,'MI')-TRUNC(CLOCK_OUT,'HH')),0,TRUNC(CLOCK_OUT,'MI'),TRUNC(CLOCK_OUT,'HH')+1/24)-TRUNC(CLOCK_IN,'HH'))*24
HOURS
FROM
LABOR_TICKET
WHERE
SHIFT_DATE='02-DEC-2006'
AND INDIRECT_ID IS NULL)
WHERE
TO_DATE('12/02/2006 06:00','MM/DD/YYYY HH24:MI') BETWEEN
FIXED_CLOCK_IN AND FIXED_CLOCK_OUT;
IN_USE
29
1 ROW SELECTED
While the above is not the final solution, it will hopefully give you
some ideas. You should be able to use a table with at least 24 rows to
generate a counter to step through each hour of the day. For example:
SELECT
ROWNUM MY_COUNTER
FROM
LABOR_TICKET
WHERE
ROWNUM<=24;
MY_COUNTER
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
24 ROWS SELECTED If you wrap the above into an inline view and join it with the previous SQL statement, you could step through each of the hours in the day.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Sun Dec 03 2006 - 08:55:16 CST
 |
 |