Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Unanswered: SQL Guidance please oh wise ones...

    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
    Attached Thumbnails Attached Thumbnails SQLTrouble.jpg  
    Last edited by purpendicular; 03-07-05 at 11:53. Reason: add detail

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    55
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    x minutes will always be x minutes, you must be doing something else not shown

    did you try the 3 queries?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    55
    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)
    Attached Thumbnails Attached Thumbnails 3-9unfiltered.JPG   3-9filtered.JPG  
    Last edited by purpendicular; 03-09-05 at 12:25. Reason: add details

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your last two jpgs show me that the sum for unfiltered is greater than the sum for filtered, which makes perfect sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    55
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2004
    Posts
    55
    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.

  10. #10
    Join Date
    Mar 2004
    Posts
    55
    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 <>''
    Attached Thumbnails Attached Thumbnails resultweb.JPG  

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a. ROUND(X,2)
    b. COALESCE(X,100)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •