More one problem I found in SQL:

Field UPTIME, result -499 "%"    while I don't use DESC in ORDER BY, but when I use DESC,
the result is correct! = 99,93 "%"

DBISam with BUG????????? Whats is wrong?

Roger Oliveira
roger@puxtreme.com.br
Underline Informática.
www.puxtreme.com.br


================================================================================
SQL statement (Executed with 4.25 Build 5)
================================================================================

SELECT CAST(D.DTHIN,DATE) DATA,SUM(IF(D.FK_RESPONSIBLE = 1 THEN SECONDS ELSE
0)) MANUTENCAO,
SUM(IF(D.FK_RESPONSIBLE = 2 THEN SECONDS ELSE 0)) PRODUCAO,
SUM(IF(D.FK_RESPONSIBLE = 5 THEN SECONDS ELSE 0)) ESPERA,
SUM(IF(D.FK_RESPONSIBLE = 6 THEN SECONDS ELSE 0)) BLOQUEIO,
SUM(IF(D.FK_RESPONSIBLE IN (1,2,5,6) THEN SECONDS ELSE 0)) TOTAL,
SUM(IF(D.FK_RESPONSIBLE IN (1,2,5,6) THEN SECONDS ELSE 0)) /
AVG(C.MINUTES_AVAILABLE) DOWNTIME,
100 - SUM(IF(D.FK_RESPONSIBLE IN (1,2,5,6) THEN SECONDS ELSE 0)) /
AVG(C.MINUTES_AVAILABLE) UPTIME,
AVG(C.MINUTES_AVAILABLE) MINUTOS_DISPONIVEIS FROM TAG_DEBUGGED D INNER JOIN
TAG_PATTERN P ON (D.TAGNAME = P.TAGNAME)
INNER JOIN CALENDAR C ON (D.DTHIN > C.START AND D.DTHOUT < C.END AND C.SHIFT =
D.SHIFT)
WHERE D.FK_RESPONSIBLE IN (1,2,3,4) AND PRODUCTIVE = 'S' AND D.AREA_ID IN(1,2,
3)
AND D.DTHIN >= '2007-10-08 16:00:00' AND D.DTHOUT <= '2008-02-24 01:30:00'
GROUP BY DATA  ORDER BY DOWNTIME

Tables Involved
---------------

TAG_DEBUGGED (D) table opened shared, has 1481 rows
TAG_PATTERN (P) table opened shared, has 143 rows
CALENDAR (C) table opened shared, has 2193 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

DATA

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

DOWNTIME ASC

WHERE Clause Execution
----------------------

The expression:

D.FK_RESPONSIBLE IN (1 , 2 , 3 , 4) AND PRODUCTIVE = 'S' AND D.AREA_ID IN (1 ,
2 , 3) AND D.DTHIN >= '2007-10-08 16:00:00' AND D.DTHOUT <= '2008-02-24 01:30:00'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 1481 rows or index keys,
costs 737241 bytes, and will be applied to the TAG_DEBUGGED table (D) before
any joins

Join Ordering
-------------

The driver table is the TAG_DEBUGGED table (D)

The TAG_DEBUGGED table (D) is joined to the TAG_PATTERN table (P) with the
INNER JOIN expression:

D.TAGNAME = P.TAGNAME

The TAG_DEBUGGED table (D) is joined to the CALENDAR table (C) with the INNER
JOIN expression:

D.DTHIN > C.START AND D.DTHOUT < C.END AND D.SHIFT = C.SHIFT

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

D.TAGNAME = P.TAGNAME

is OPTIMIZED

The expression:

D.DTHIN > C.START AND D.DTHOUT < C.END AND D.SHIFT = C.SHIFT

has been rewritten and is PARTIALLY-OPTIMIZED

================================================================================
>>>>> 6 rows affected in 0,735 seconds
================================================================================

Roger,

<< More one problem I found in SQL:

Field UPTIME, result -499 "%"    while I don't use DESC in ORDER BY, but
when I use DESC, the result is correct! = 99,93 "%" >>

Could you send me the tables that you're using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

OK, The tables is in Attach.

PS: Please, I need one soluction for this.

Roger Oliveira
roger@puxtreme.com.br
Underline Informática.
www.puxtreme.com.br

Roger,

<< OK, The tables is in Attach. >>

Okay, I found the problem and a fix will be available hopefully this week.
The issue is the fact that the ORDER BY disrupting the AVG and STDDEV
calculations.

--
Tim Young
Elevate Software
www.elevatesoft.com