SQL Statement and Excel

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)

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 you’ve 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 won’t 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)
>
>
>
>

Thanks for the reply, John.

I've since changed the SQL around and added a new column. Since I'm
populating the table from a VB program with the downtime percent, I simply
added another column that includes PercentUp. Therefore, I have a computed
column now that includes PercentDown and PercentUp. This will save me from
having to mess around with the calculations and junk from SQL.

I also converted the datatype (as suggestioned by someone a few days ago)
from Decimal(5,2) to Integer and simply divid/multiple the value by 100 to
add/remove the decimal.

Thus far, everything seems to work and looks good.

I was really hoping I could get Excel to prompt for the "Year" when the
query was run vs having someone go to MS Query tool each time and changing
the parameter to the SP that I'm running. I guess using SQL directely
versus the GUI loses Excel's ability to prompt for parameters; which sucks.

The only problem I had with the SQL I provided below from orginal email is
that the COUNT() was not correct. It was returning the COUNT for the entire
year instead of the month. It took me a few hours of trying various things
before I got the result I wanted. Essentially, I had to add another CASE for
the division and returned 1 for true and NULL for false. The NULL bypassed
any division by zero when there were months without totals.

I've defintely learned some things these past few days that I didn't know
last week this time.



"John Mansfield" <[email protected]> wrote in message
news:[email protected]...
> 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 you've 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 won't 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)
>>
>>
>>
>>

DavidM wrote:
> I guess using SQL directely versus the GUI loses Excel's ability to
prompt
> for parameters

Yes, if MS Query cannot interpret the SQL according to its own SQL
syntax. When this happens you get the message 'Can't display
graphically' (whatever than means) and you will not be able to use its
parameters functionality. Syntax (ODBC or otherwise) to call a stored
proc have this effect.

Jamie.

--