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.

Answer 1

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

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Pleas Try

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

---

Thanks, Sachin Surve

Answer 3

Answer 4

try

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

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 5

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

Answer 6

 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

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

 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