CODEWITHSUNDEEP
sql-server
Krazy Klauz
Krazy Klauz

Reputation: 29

MS SQL change Cast count VarChar to %

In the last line of the code I have listed below, The current result is blank because the result is less than 1. I need the results to display as a percentage but I'm not sure how. Any suggestions are greatly appreciated?

SELECT
 '1,*'+char(13)+char(10) 
+'80,1006058'+char(13)+char(10)
+'100,10'+char(13)+char(10)
+'2405,'+cast(count(distinct adt.PAT_ENC_CSN_ID) / 420 as varchar(18))+char(13)+char(10) --Census events --as varchar(10)

Upvotes: 1

Views: 393

Answers (3)

dbbri
dbbri

Reputation: 224

The issue is that your count(distinct adt.PAT_ENC_CSN_ID) returns an integer value and then you divide by 420. Another integer.

If you cast the count distinct as a decimal or float, this should solve your issue. CAST(COUNT(DISTINCT adt.PAT_ENC_CSN_ID) AS FLOAT).

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17136

Just replace this part

     cast(count(distinct adt.PAT_ENC_CSN_ID) / 420 as varchar(18))

with

     cast(count(distinct adt.PAT_ENC_CSN_ID) / 420.00 as varchar(18))

Note that all we did was turn 420 to 420.00 to suggest SQL to retain decimal part and not treat the result as integer.

As count return integer values and integer/integer is an integer but integer/decimal is decimal

Upvotes: 1

Thomas G
Thomas G

Reputation: 10216

SELECT
 '1,*'+char(13)+char(10) 
+'80,1006058'+char(13)+char(10)
+'100,10'+char(13)+char(10)
+'2405,'+cast( cast(count(distinct adt.PAT_ENC_CSN_ID) AS FLOAT) / cast(420 AS FLOAT) as varchar(18))+char(13)+char(10) --Census events --as varchar(10)

You should cast() your numbers as float otherwise they are considered as integers and you don't have decimals in your division because the result is also considered integer

Upvotes: 1

Related Questions