How to Calculate Percentage using two Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to Calculate Percentage using two Columns

Author

Topic

vandan04
Starting Member

2 Posts

Posted - 2013-12-09 : 18:37:54

Please can someone help me on how to calculate percentage using two column from two table after join the table together.

My script is returning 0 value instead of percentage.
Here is my script

SELECT CAST ((cast(SUM(P1.Activity - P2.Activity)as numeric(9,4)) /P1.Activity)*100 as numeric(9,4))
FROM (
SELECT Count(*) As Activity,
month([CDS Activity Date]) as CDS_Month
FROM [AE_Main_London] T1
left join (SELECT [Generated Identifier] FROM [Validation]
where ValidationCode ='5A_') T2 on
T1.[Generate Identifier] =T2.[Generate Identifier]
group by month([CDS Activity Date])) P1 INNER JOIN (
SELECT Count(*) As Activity,
month([CDS Activity Date]) as CDS_Month
FROM [AE_Main] T1
right join (SELECT [Generated Identifier] FROM [Validation]
where ValidationCode ='5A_') T2 on
t1.[Generated Identifier] =T2.[Generated Identifier]
Group by month([CDS Activity Date])
)P2
ON P1.CDS_Month=P2.CDS_Month
GROUP BY P1.Activity

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 22:31:46

try

 
....
CAST (SUM(P1.Activity - P2.Activity) *100.0/P1.Activity as numeric(9,4))
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vandan04
Starting Member

2 Posts

Posted - 2013-12-10 : 13:15:47

Thanks Visakh16.
The code work perfectly well.
I am really grateful for your help.

Thanks again
bye bye

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 05:32:40

quote:
Originally posted by vandan04

Thanks Visakh16.
The code work perfectly well.
I am really grateful for your help.

Thanks again
bye bye

you're welcome
see reason here
http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources