ExcelBanter - View Single Post

David,

Since your SQL statement is giving you the numbers that you want with the
exception of future data i.g Feb. 2005 through Dec. 2005, rather than trying
to modify the SQL statement even more could you try something like this:

(1) Have the SQL statement feed the Excel spreadsheet (like I think you
have now). As youve noted the data will be incorrect for Feb 2005 €“ Dec
2005 because it will show 100s.
(2) Create a second source range in your worksheet for the data on your
chart. Have the second source range refer to the first SQL data source via
formulas. You can then add IF statements within those formulas that contain
logic to say something like - if the data refers to a current or prior month
then show it but if the data refers to a future month put a zero in the cell
instead.
(3) Use the new second range as the source for your chart. If built
correctly, the present and prior months will show values but the future
months will show zeros plus you wont have to modify your SQL statement.

----
Regards,
John Mansfield
http://www.pdbook.com

"DavidM" wrote:

Hello, all -- I got the below SQL to finally work and it will properly link
within Excel 2000. I am having one problem and I hope someone can help me.

The below SQL SUM(PercentDown) column (which contains the percentage a
system was down) and divides by COUNT(PercentDown) to determine the average
percent down for entire month.

Figures are corret.

My only problem now is that I want to show the percent up instead of
percentage down. In order to accomplish this, I subtract the value from
100. This works, but if there is no data for that month, the default
returned is 100. This could be misleading to some--especially if I run the
SQL for this year... JAN would have various percentage values, but FEB-DEC
will have 100%. Not cool.

Originally, I did not have the 100- in the SQL, but I could not figure out a
way to make Excel do this within a cell.

Below is my SQL - maybe someone can spot something or tell me a better way
to figure out the percentage and have it displayed within Excel.

SELECT [DB] AS ' '
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 1 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'JAN'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 2 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'FEB'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 3 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'MAR'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 4 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'APR'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 5 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'MAY'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 6 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'JUN'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 7 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'JUL'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 8 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'AUG'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 9 THEN PercentDown ELSE
0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'SEP'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 10 THEN PercentDown
ELSE 0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'OCT'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 11 THEN PercentDown
ELSE 0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'NOV'
,CAST(100-(SUM(CASE WHEN Month(ProcessDate) = 12 THEN PercentDown
ELSE 0 END)/COUNT(PercentDown)) * 100 AS DECIMAL(8,2)) AS 'DEC'
FROM tblDowntime2
WHERE Year(ProcessDate) = '2004'
GROUP BY [DB]
ORDER BY [DB]

Sample output for 2004. JAN-JUN data is not available. So assumes 100%
which it should read 0.00%

JAN FEB MAR APR MAY JUN JUL
AUG SEP OCT NOV DEC
---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ----------
SYS 100.00 100.00 100.00 100.00 100.00 100.00 99.99
99.99 99.77 100.00 100.00 99.80
SYS2 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 99.83 99.99 100.00 99.81
SYS3 100.00 100.00 100.00 100.00 100.00 100.00
100.00 99.86 99.75 99.98 99.97 99.74
SYS4 100.00 100.00 100.00 100.00 100.00 100.00 93.29
81.10 81.71 84.40 84.71 84.08
SYS5 100.00 100.00 100.00 100.00 100.00 100.00
100.00 99.97 99.86 99.97 100.00 99.83
SYS6 100.00 100.00 100.00 100.00 100.00 100.00
100.00 99.92 99.37 99.92 99.96 99.72
SYS7 100.00 100.00 100.00 100.00 100.00 100.00
100.00 99.91 99.85 99.99 99.98 99.96
SYS8 100.00 100.00 100.00 100.00 100.00 100.00 99.96
99.82 99.84 99.39 99.98 99.81

(8 row(s) affected)

Sample output for 2005. Only JAN is available - which is 100% so far.
However, FEB-DEC is assumed 100% which is should read 0.00%.
JAN FEB MAR APR MAY JUN JUL
AUG SEP OCT NOV DEC
---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ----------
SYS 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS2 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS3 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS4 .00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS5 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS6 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS7 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00
SYS8 100.00 100.00 100.00 100.00 100.00 100.00
100.00 100.00 100.00 100.00 100.00 100.00

(8 row(s) affected)