I have a report that looks like this:
Query Name Attempts Successes Failures SuccessProportion
First Query 1 0 1 0/1
Second Query 1 0 1 0/1
Third Query 2 1 1 1/2
Fourth Query 1 1 0 1/1
Fifth Query 1 0 1 0/1
Sixth Query 1 0 1 0/1
this is my code that creates the report:
l_strsql = "SELECT Query_Name, " +
" Attempts, " +
" Successes, " +
" Failures, " +
" CAST([Successes] AS NVARCHAR(10)) + ' / ' + CAST([Attempts] AS NVARCHAR(10)) AS Proportion " +
" FROM ( " +
"SELECT generate_query AS Query_Name, " +
" sum(case when RecNum is NOT NULL then 1 else 0 end) AS Attempts, " +
" sum(case when export_TIR = 'Yes' then 1 else 0 end) AS Successes, " +
" sum(case when export_TIR = 'No' then 1 else 0 end) AS Failures " +
" FROM tbl_TPF " +
" WHERE export_type = 'Excel' " +
" GROUP BY generate_query " +
" ) Q ";
How can I get the SuccessProportion to show as a decimal with 2 places after the decimal? - is it possible?
" CAST([Successes] AS NVARCHAR(10)) + ' / ' + CAST([Attempts] AS NVARCHAR(10)) AS Proportion
That line is setting it to a varchar, which is the 1/2 format you are seeing.
" CAST([Successes] AS numeric(8,2)) / CAST([Attempts] AS numeric(8,2)) AS Proportion
that should give a numeric value as a success/attempts. There is no 0 handling (attempts = 0 means a div by 0 error), but I suspect from your query, that won't happen
edit - one final cast to get to the final format you want:
" cast(CAST([Successes] AS numeric(8,2)) / CAST([Attempts] AS numeric(8,2)) as numeric(8,2))"