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
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:
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
End of code...
The values for fullstartdate and fullenddate are set to 2005-01-01 and 2005-
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
Last edited by purpendicular; 03-07-05 at 10:53.
Reason: add detail
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.
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)
Last edited by purpendicular; 03-09-05 at 11:25.
Reason: add details
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 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.
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
WHERE tbl_dt_event.dt_event_initiated >= 'startdate' and tbl_dt_event.dt_event_initiated <= 'enddate' AND tbl_dt_event.dt_event_signoff <>''