I had written a webpage that needed to do a single SQL query and return the information. The information had a key column named ‘AssetnameFindingDetailURL’ and computations needed to be done based on a combination of a few other columns ‘StatusDisplayOverride’ and ‘Severity’. To achieve this I originally just did a HUGE (1110 lines) SQL with nested SELECT statements. This proved to be very slow and almost impossible to understand. I decided it was possible to shorten it so i spent about 1 hour having a go at using a CASE statement. The results were quite good. I shortened the execution time from .4 of a second down to .1 of a second and from 1110 lines of SQL to 110 lines of SQL. I am sure this can be shortened even more but it is beyond my interest at the moment to make it shorter without any procedures/etc. Hopefully someone will find this usefule.
How the Syntax changed
ROUND
(
(
(
(
SELECT
COUNT(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
)*100.0
)/COUNT(severity)
),2
) AS 'Total_%_Done'
Was turned into
ROUND
(
COUNT
(
CASE
WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN
Severity
END
)*100.00 / COUNT(Severity), 2
) AS CatAPercent,
Example output
A|54.48|525|286|100.0|64|64|0|0|29|35|53.21|389|207|47|128|60|147|21.43|70|15|36|18|12|3|0.0|2|0|2|0|0|0
B|73.91|23|17|100.0|3|3|0|0|3|0|76.92|13|10|0|2|10|0|57.14|7|4|3|0|4|0||0|0|0|0|0|0
C|41.67|12|5|100.0|5|5|0|0|5|0|0.0|3|0|3|0|0|0|0.0|3|0|3|0|0|0|0.0|1|0|1|0|0|0
D|12.5|16|2|100.0|1|1|0|0|1|0|0.0|6|0|6|0|0|0|12.5|8|1|7|0|1|0|0.0|1|0|1|0|0|0
E|87.5|8|7|100.0|1|1|0|0|1|0|85.71|7|6|0|1|6|0||0|0|0|0|0|0||0|0|0|0|0|0
F|12.5|8|1|100.0|1|1|0|0|1|0|0.0|4|0|4|0|0|0|0.0|3|0|3|0|0|0||0|0|0|0|0|0
G|100.0|13|13|100.0|2|2|0|0|2|0|100.0|9|9|0|0|9|0|100.0|2|2|0|0|2|0||0|0|0|0|0|0
H|81.4|43|35|100.0|12|12|0|0|4|8|81.48|27|22|0|5|17|5|25.0|4|1|2|1|1|0||0|0|0|0|0|0
I|37.5|48|18|100.0|11|11|0|0|3|8|25.0|28|7|18|3|2|5|0.0|9|0|9|0|0|0||0|0|0|0|0|0
J|54.81|312|171|100.0|18|18|0|0|6|12|54.28|269|146|0|117|16|130|28.0|25|7|0|17|4|3||0|0|0|0|0|0
K|40.48|42|17|100.0|10|10|0|0|3|7|30.43|23|7|16|0|0|7|0.0|9|0|9|0|0|0||0|0|0|0|0|0
Here is the Short version of the SQL (CASE statement).
SELECT
'ALL DEVICES',
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN Severity END)*100.00 / COUNT(Severity), 2 ) AS CatAPercent,
COUNT(Severity) AS CatAT,
COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') THEN Severity END ) AS CatAD,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='1-Category I' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ), 2 ) AS Cat1Percent,
COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ) AS Cat1T,
COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') AND Severity='1-Category I' THEN Severity END ) AS Cat1D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='1-Category I' THEN Severity END ) AS Cat1O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='1-Category I' THEN Severity END ) AS Cat1PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1S,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='2-Category II' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ), 2 ) AS Cat2Percent,
COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ) AS Cat2T,
COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='2-Category II' THEN Severity END ) AS Cat2D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='2-Category II' THEN Severity END ) AS Cat2O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='2-Category II' THEN Severity END ) AS Cat2PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2S,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='3-Category III' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ), 2 ) AS Cat3Percent,
COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ) AS Cat3T,
COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='3-Category III' THEN Severity END ) AS Cat3D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='3-Category III' THEN Severity END ) AS Cat3O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='3-Category III' THEN Severity END ) AS Cat3PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3S,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='4-Category IV' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ), 2 ) AS Cat4Percent,
COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ) AS Cat4T,
COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='4-Category IV' THEN Severity END ) AS Cat4D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='4-Category IV' THEN Severity END ) AS Cat4O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='4-Category IV' THEN Severity END ) AS Cat4PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4S
FROM
vms
UNION ALL
SELECT
UPPER(AssetNameFindingDetailURL) as 'Asset Name',
CatAPercent,
CatAT,
CatAD,
Cat1Percent,
Cat1T,
Cat1D,
Cat1O,
Cat1PR,
Cat1TBS,
Cat1S,
Cat2Percent,
Cat2T,
Cat2D,
Cat2O,
Cat2PR,
Cat2TBS,
Cat2S,
Cat3Percent,
Cat3T,
Cat3D,
Cat3O,
Cat3PR,
Cat3TBS,
Cat3S,
Cat4Percent,
Cat4T,
Cat4D,
Cat4O,
Cat4PR,
Cat4TBS,
Cat4S
FROM
(
SELECT
AssetNameFindingDetailURL,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN Severity END)*100.00 / COUNT(Severity), 2 ) AS CatAPercent,
COUNT(Severity) AS CatAT,
COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') THEN Severity END ) AS CatAD,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='1-Category I' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ), 2 ) AS Cat1Percent,
COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ) AS Cat1T,
COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='1-Category I' THEN Severity END ) AS Cat1D,
COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='1-Category I' THEN Severity END ) AS Cat1Percent,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='1-Category I' THEN Severity END ) AS Cat1O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='1-Category I' THEN Severity END ) AS Cat1PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1S,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='2-Category II' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ), 2 ) AS Cat2Percent,
COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ) AS Cat2T,
COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='2-Category II' THEN Severity END ) AS Cat2D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='2-Category II' THEN Severity END ) AS Cat2O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='2-Category II' THEN Severity END ) AS Cat2PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2S,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='3-Category III' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ), 2 ) AS Cat3Percent,
COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ) AS Cat3T,
COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='3-Category III' THEN Severity END ) AS Cat3D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='3-Category III' THEN Severity END ) AS Cat3O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='3-Category III' THEN Severity END ) AS Cat3PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3S,
ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='4-Category IV' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ), 2 ) AS Cat4Percent,
COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ) AS Cat4T,
COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='4-Category IV' THEN Severity END ) AS Cat4D,
COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='4-Category IV' THEN Severity END ) AS Cat4O,
COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='4-Category IV' THEN Severity END ) AS Cat4PR,
COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4TBS,
COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4S
FROM
vms
GROUP BY
AssetNameFindingDetailURL
) AS ABC;
Here is the Long version of the SQL (nested SELECT statements).
SELECT
'*ALL DEVICES*' as 'Asset Name',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
)*100.0
)/count(severity)
),2) as 'Total_%_Done',
count(severity) as Total,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
) as 'Total_Done',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '1-Category I'
)
),2) as '%_Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I'
) as Total_Cat1s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
) as 'Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open'
) as O_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat1,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '2-Category II'
)
),2) as '%_Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II'
) as Total_Cat2s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
) as 'Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open'
) as O_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat2,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '3-Category III'
)
),2) as '%_Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III'
) as Total_Cat3s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
) as 'Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open'
) as O_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat3,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '4-Category IV'
)
),2) as '%_Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV'
) as Total_Cat4s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
) as 'Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open'
) as O_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat4
FROM
vms
UNION ALL
SELECT
UPPER(AssetNameFindingDetailURL) as 'Asset Name',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/count(severity)
),2) as 'Total_%_Done',
count(severity) as Total,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Total_Done',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat1s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat1,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat2s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat2,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat3s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat3,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat4s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat4
FROM
vms as vw
GROUP BY AssetNameFindingDetailURL): no such column: vv.AssetNameFindingDetailURL
Array
(
[0] => Array
(
[file] => /var/www/html/itdb/php/reports.php
[line] => 811
[function] => db_execute
[args] => Array
(
[0] => PDO Object
(
)
[1] => SELECT
'*ALL DEVICES*' as 'Asset Name',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
)*100.0
)/count(severity)
),2) as 'Total_%_Done',
count(severity) as Total,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
) as 'Total_Done',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '1-Category I'
)
),2) as '%_Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I'
) as Total_Cat1s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
) as 'Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open'
) as O_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat1,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '2-Category II'
)
),2) as '%_Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II'
) as Total_Cat2s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
) as 'Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open'
) as O_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat2,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '3-Category III'
)
),2) as '%_Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III'
) as Total_Cat3s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
) as 'Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open'
) as O_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat3,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '4-Category IV'
)
),2) as '%_Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV'
) as Total_Cat4s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
) as 'Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open'
) as O_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research'
) as PR_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted'
) as TBS_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted'
) as S_Cat4
FROM
vms
UNION ALL
SELECT
UPPER(AssetNameFindingDetailURL) as 'Asset Name',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/count(severity)
),2) as 'Total_%_Done',
count(severity) as Total,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Total_Done',
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat1s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat1',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat1,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat1,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat2s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat2',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat2,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat2,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat3s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat3',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat3,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat3,
round( (
(
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)*100.0
)/ (
SELECT count(severity) FROM vms WHERE Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
)
),2) as '%_Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as Total_Cat4s,
(
SELECT
count(severity)
FROM
vms
WHERE
(StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as 'Done_Cat4',
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as O_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as PR_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as TBS_Cat4,
(
SELECT
count(severity)
FROM
vms
WHERE
Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
) as S_Cat4
FROM
vms as vv
GROUP BY AssetNameFindingDetailURL
Gheek.net 