SQL Statement and Excel

D

DavidM

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)
 
J

John Mansfield

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 said:
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)
Click to expand...
 
D

DavidM

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 said:
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 said:
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)
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculated Fields and Pivot Tables 1
ACCESS SQL ? 4
Display Numbers as Words 3
Case Statement Help 2
Numaric to Inword in excel by VB 1
case statement within aggregate 2
3 views becoming unweildy 0
Notice few bugs 0

Top