0

I'm trying to get the percentage for all sites... I tried UCSF_P but it's wrong

enter image description here

SELECT A.Year
    ,A.Month
    ,(sum(A.BCHO) * 100 / NULLIF((sum(B.BCHO)), 0)) AS BCHO
    ,
    --SELECT FORMAT((37.0/38.0),'P3') as [ThreeDecimalsPercentage]
    FORMAT((sum(A.UCSF)) * 100 / (sum(B.UCSF)), 'P2') AS UCSF_P
    ,-------------------This is wrong
    (sum(A.UCSF) * 100 / (sum(B.UCSF))) AS UCSF
    ,(sum(A.UCLA) * 100 / (sum(B.UCLA))) AS UCLA
    ,(sum(A.UCD) * 100 / (sum(B.UCD))) AS UCD
    ,(sum(A.UCI) * 100 / (sum(B.UCI))) AS UCI
    ,(sum(A.UCSD) * 100 / (sum(B.UCSD))) AS UCSD
FROM Denials_Scrub_Final_V A
INNER JOIN Claims_Final_V B ON B.Year = A.Year
    AND A.Month = B.Month
GROUP BY A.Year
    ,A.Month
    ,A.UCSF
    ,B.UCSF
ORDER BY A.Year
    ,A.Month
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
LEARNDATAsCI
  • 7
  • 1
  • 6
  • I think you might need to add a bit more info to this question; I can't work out what it is you're asking.. "Decimal percentage of two tables" makes no sense – Caius Jard Jul 17 '20 at 16:34
  • for UCSF_P for the year 2019 and month 12.....I want to see 3.38% but Im seeing 300.00% I want to get the percent from 2 views. – LEARNDATAsCI Jul 17 '20 at 16:51

2 Answers2

0

SQL Server does integer division. To get decimal places, use 100.0 rather than 100:

sum(A.UCSF) * 100.0 / sum(B.UCSF)

If you specifically want 2 digits in the result, either use round() or convert to a decimal, such as decimal(5, 2) or use format().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

This is how I formatted it...

SELECT 
A.Year,
A.Month,
FORMAT((sum(A.BCHO)*100/NULLIF((sum(B.BCHO)),0))/100.0,'P') as BCHO,  
FORMAT((sum(A.UCSF))*100.0/(sum(B.UCSF))/100.0,'P') as UCSF,
FORMAT((sum(A.UCD))*100.0/(sum(B.UCD))/100.0,'P') as UCD,
FORMAT((sum(A.UCLA))*100.0/(sum(B.UCLA))/100.0,'P') as UCLA,
FORMAT((sum(A.UCI))*100.0/(sum(B.UCI))/100.0,'P') as UCI,
FORMAT((sum(A.UCSD))*100.0/(sum(B.UCSD))/100.0,'P') as UCSD
FROM Denials_Scrub_Final_V A
INNER JOIN  Claims_Final_V B
ON B.Year = A.Year
AND A.Month = B.Month
GROUP BY
A.Year,
A.Month,
A.UCSF,
B.UCSF
ORDER BY
A.Year,
A.Month
LEARNDATAsCI
  • 7
  • 1
  • 6