I've been working with the following sql code since last friday morning trying to figure why it is not working as I would like it.
I believe that the values I am getting are correct except for
FullTimePeriod and FullPercentageUptime.
I noticed this because the values change when I add a timeframe requirement
such as:
WHERE dt_event_initiated >= 'fullstartdate' and dt_event_initiated <= 'fullenddate'
and also if I simply filter between planned and unplanned events.
The FullTimePeriod changes (and the sql for that is the same I used for the FullPercentageUptime so that is incorrect also).

Start of code:

SELECT
sec_to_time(sum(unix_timestamp(dt_event_diffused) - unix_timestamp(dt_event_initiated))) as hmsFullDowntime,

sum(unix_timestamp(dt_event_diffused) - unix_timestamp(dt_event_initiated)) as secFullDowntime,

sum(unix_timestamp(date_add('fullenddate',interval 1 day)) - unix_timestamp('fullstartdate')) as FullTimePeriod,

100 *(sum(unix_timestamp(dt_event_diffused) - unix_timestamp(dt_event_initiated))) / (sum(unix_timestamp(date_add('fullenddate',interva l 1 day)) - unix_timestamp('fullstartdate'))) * -1 +100 as FullPercentageUptime

FROM tbl_dt_event

End of code...

The values for fullstartdate and fullenddate are set to 2005-01-01 and 2005-
01-31 respectively.

When I add the statement
WHERE dt_downtime_planned = 'Yes' I get a different value for FullTimePeriod
and when I replace the 'Yes' with 'No' I get another different value.

Shouldn't the value for FullTimePeriod remain the same throught each of the changes, it should be pulling the exact same values each time.
My results are listed below

go back to basics, and make sure your "filters" account for all rows

obtain these 3 values --

select count(*) from tbl_dt_event
select count(*) from tbl_dt_event where dt_downtime_planned = 'Yes'
select count(*) from tbl_dt_event where dt_downtime_planned = 'No'

if these don't add up, you have one or more NULLs in the dt_downtime_planned column

Rudy,
Thanks, but what I really am looking for is an accurate value for the seconds within the time range selected by the user, it seems it is not remaining constant. User imput January 1-31 has x minutes within the month, I want to keep that constant wether I'm searching for planned downtimes, unplanned downtimes or all downtimes. That is the value that keeps changing on me and I don't understand why.

x minutes will always be x minutes, you must be doing something else not shown

did you try the 3 queries?

The values for the 3 queries are correct.
I'm including here the sql and results for the time issue so you can see exactly what is happening.
(Edit. FYI, there are no events outside of the default value range)

your last two jpgs show me that the sum for unfiltered is greater than the sum for filtered, which makes perfect sense

But what is causing the InputtedTimePeriod value to change?
Is it taking the time between the first planned incident and the last planned incident?
I'm not understanding why the difference in time between the startdate and enddate would change, shouldn't that remain constant since it's pulling 2004-01-01 and 2006-01-01 respectively?

i have no idea, but i'm confident i could find the problem if i had your entire table to work with

however, please don't post it, i'm sure you can find the answer yourself

remember the 3 queries i asked you to run?

you need to set up something similar for your sums

also, make sure you use the correct date range

if your date fields have time components, then you'll want open-ended upper ranges

i.e. don't use this --

... WHERE somedate BETWEEN '2005-03-01' AND '2005-03-31'

because that will not catch any rows on the 31st other than the row at midnight, if any

instead, do this --

... WHERE somedate >= '2005-03-01' AND somedate < '2005-04-01'

I just got notice of my next project... it's going to be a web based timeclock for the dept employees to use.. I'm going to see if I can have the company pay for a flight to Toronto for some expert training... yeah right.
Thanks for your help Rudy, I'll keep plugging on the problem at hand for now.

Rudy,
I got it to work. Whew.
I did not calculate the TimePeriod within this query though, I passed the value on from the previous page already calculated to seconds as the variable TimePeriod instead

All my testing is working as I imagined and the only issue I have remaining is
a. how to shorten the percentage to only a few decimals and
b. how to show it as 100 % when there are no events within a time period (right now the result is blank).
How can I accomplish this?


SELECT count(*), 100 + sum(((unix_timestamp(tbl_dt_event.dt_event_diffuse d) - unix_timestamp(tbl_dt_event.dt_event_initiated)) / 'TimePeriod') * -100) as AllPercentage
FROM tbl_dt_event
WHERE tbl_dt_event.dt_event_initiated >= 'startdate' and tbl_dt_event.dt_event_initiated <= 'enddate' AND tbl_dt_event.dt_event_signoff <>''

a. ROUND(X,2)
b. COALESCE(X,100)