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 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) |
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" wrote in message ... 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. -- |
All times are GMT +1. The time now is 01:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com