How to sum column in oracle 9 to include portion of first and last row and full amout of remaining rows

Question

0.00/5 (No votes)

See more:

This is kind of hard to explain. I am trying to sum an integer column based on a date column. The problem is that the data in my date column rarely matches up exactly with my where clause so I need to modify the results of just the first row and last row. Here is some example data:

LogTime,Machine,Status,DownTimeCode,Duration

06/15/2017 05:50:00 AM,500,2,null,60
06/15/2017 06:01:00 AM,500,3,10,660
06/15/2017 06:03:00 AM,500,2,null,120
06/15/2017 06:04:00 AM,500,3,12,60
06/15/2017 07:01:00 AM,500,3,13,3420

First let me briefly explain the data:
The LogTime column contains the time the event was logged in the database which is at the end of each event. The duration column contains the time in seconds the event lasted. So for the first row, the event ended at 5:50:00 AM with a duration of 60 seconds means the event started at 5:49:00 AM.

What I have tried:

I am trying to sum the duration column based on different downtimecodes and different status's for a certain time period:

For example:
Select Sum(duration)
From Machines_Log
Where machine = '500'
and logtime > TO_DATE('06/15/2017 06:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
and logtime <= TO_DATE('06/15/2017 07:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
and status = 3

This will return (660+60) = 720
However, this answer is not what I need. The 660 seconds contains 600 seconds of time before 6:00:00 AM and 60 seconds after 6:00:00 AM. I am only interested in the 60 seconds after. A similar thing occurs on the last row. I want the portion of the 3420 seconds that is before 7:00:00 AM. In this case, I only want (3420-60) = 3360 seconds. So my Sum should total to 60+60+3360 = 3480

I am currently doing this with multiple queries via a VB.net data reader where I read each row one by one. If it is the first row, I adjust the duration based on the logtime and the start date in my Where clause. When I get the last row, I launch a 2nd query to find the next row that has a logtime greater then the last logtime I read with my datareader. I then only take a portion of the duration from this result based on the end date in my where clause. I am sure there has got to be a better and more efficient way to do this but my knowledge of SQL is limited to just basic queries.

Posted 15-Jun-17 6:14am

theskiguy

Updated 16-Jun-17 23:36pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RAMASWAMY EKAMBARAM · Accepted Answer · 2017-06-16T23:36:00

SQL

with dtrange as
(
select to_date('15-jun-2017 06:00', 'dd-mon-yyyy hh24:mi') range_start,  to_date('15-jun-2017 07:00', 'dd-mon-yyyy hh24:mi') range_end
from dual
),
recs_in_range as
(
select greatest(range_start, downstart) downstart, least(downend, range_end) downend
from
(
select (logtime - duration / 86400) downstart, logtime downend,  range_start, range_end
from machines_log, dtrange
where machine = '500'
and status = 3
and not (logtime < range_start
or (logtime - duration / 86400) > range_end)
)
)
select round(sum((downend - downstart) * 86400))
from recs_in_range
-- 86400 == 24 * 60 * 60 i.e. no of seconds in a day
--