# division problem and attaching a % symbol in Query.

### Question

• @AVD AS AvailableDays,

(@AVD - Cast(SUM(T.ActualWorkBillable) as int)) as AfterHolidays,

((@AVD - Cast(SUM(T.ActualWorkBillable) as int)) / @AVD) as Percentage

These three fields which I am using in my query

I want to divide "AfterHolidays" with "AvailableDays" and show the value in Percentage column with two decimal places and with '%' sign.

I am doing this but there is no decimal places and no '%' symbol.

How to I do this.

Tuesday, September 18, 2012 7:24 AM

• try

cast(cast((((@AVD*1.0) - SUM(T.ActualWorkBillable) ) / (@AVD*1.0)) as decimal(10,2))as varchar(20) )+'%' Percentage

vt

• Marked as answer by Wednesday, September 19, 2012 8:47 AM
Tuesday, September 18, 2012 7:37 AM
• Replace varchar(15) with varchar(Max) or Varchar(4000)

sample code

```declare @AVG int,
@Val int
set @AVG=123456
set @Val=10000000

select convert(varchar,cast(cast(@Val AS decimal(19,2))/cast(@AVG AS decimal(19,2)) AS decimal(10,2)))```

Thanks, Sachin Surve

• Marked as answer by Wednesday, September 19, 2012 8:47 AM
Tuesday, September 18, 2012 7:45 AM

### All replies

• pls try

((@AVD*1.0) - SUM(T.ActualWorkBillable)) as AfterHolidays,

cast((((@AVD*1.0) - SUM(T.ActualWorkBillable) ) / (@AVD*1.0)) as varchar(20) )+'%' Percentage

VT

Tuesday, September 18, 2012 7:27 AM
• Pleas Try

Convert(VARCHAR,((@AVD - Cast(SUM(T.ActualWorkBillable) as Float) / CAST(@AVD as Float))+'%' as Percentage

Thanks, Sachin Surve

Tuesday, September 18, 2012 7:29 AM
• Tuesday, September 18, 2012 7:30 AM
• try

cast(cast((((@AVD*1.0) - SUM(T.ActualWorkBillable) ) / (@AVD*1.0)) as decimal(10,2))as varchar(20) )+'%' Percentage

vt

• Marked as answer by Wednesday, September 19, 2012 8:47 AM
Tuesday, September 18, 2012 7:37 AM
• Replace varchar(15) with varchar(Max) or Varchar(4000)

sample code

```declare @AVG int,
@Val int
set @AVG=123456
set @Val=10000000

select convert(varchar,cast(cast(@Val AS decimal(19,2))/cast(@AVG AS decimal(19,2)) AS decimal(10,2)))```

Thanks, Sachin Surve

• Marked as answer by Wednesday, September 19, 2012 8:47 AM
Tuesday, September 18, 2012 7:45 AM
•  CAST
(@AVD as Decimal(19,2)) - CONVERT(varchar,CAST(CAST(SUM(T.ActualWorkBillable) as decimal(19,2)))) / CAST(@AVD as decimal(19,2)) as Percentage

• Edited by Tuesday, September 18, 2012 8:26 AM
• Marked as answer by Wednesday, September 19, 2012 8:47 AM
• Unmarked as answer by Thursday, September 20, 2012 2:12 AM
Tuesday, September 18, 2012 8:26 AM
• what is the datatype of T.ActualWorkBillable as well as @AVD? are they of type of INT?

```CONVERT(
NVARCHAR(100),
(
CONVERT( INT, @AVD )	-	(	SUM	(	CONVERT( INT, T.ActualWorkBillable)	)	/ CONVERT( INT, @AVD )		)
)
+ '%'
as Percentage```

regards

joon

Tuesday, September 18, 2012 8:33 AM
• This is working correct and getting percentage .

but tell me how to I format this to percentage upto 2 decimal and need to show % symbol in collumn.

Tuesday, September 18, 2012 8:45 AM
• This is working correct and getting percentage .

but tell me how to I format this to percentage upto 2 decimal and need to show % symbol in collumn.

CONVERT(VARCHAR,CAST(CAST(@ADV - SUM(T.ActualWorkBillable) as Decimal(19,2))/CAST(@AVD As Decimal(19,2))*100 AS DECIMAL(19,2)))+'%'

Thanks, Sachin Surve

Tuesday, September 18, 2012 9:08 AM
• ```CONVERT(
NVARCHAR(100),
(
CONVERT( decimal (35,2) , @AVD )	-	(	SUM	(	CONVERT( decimal (35,2), T.ActualWorkBillable)	)	/ CONVERT( decimal (35,2), @AVD )		)
)
+ '%'
as Percentage```

you can decide, where you want to apply the decimal points, either on the sum part or for whole calculation.

regards

joon

Tuesday, September 18, 2012 10:26 AM
•  Netiquette in an SQL forum is to post DDL. Rows are not fields. Display formatting is done in the presentation layer. Puttign that percent sign on this is the worst way to write SQL. You do not know about INTEGER math in SQL; if you use integers, you will an integer results. Here is a guess at what you meant.

DECLARE @in_available_day_cnt INTEGER;

DECLARE @after_holidays_day_cnt INTEGER;

SET @after_holidays_day_cnt

= (@in_available_day_cnt

- SUM(T.actual_work_billable);

DECLARE @after_holidays_day_percent DECIMAL (12,3);

SET @after_holidays_day_percentage

= (@after_holidays_day_cnt

- SUM(T.Actual_work_billable)

/ @after_holidays_day_cnt;

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Tuesday, September 18, 2012 11:20 AM