0 or 1 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

0 or 1

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-13 : 09:23:01

Hi,
I am experiencing a slight issue with the code below.
Third line from bottom
I have 'a.reportcount/b.reportcount*100'
This should yield 67.2,58.4....
But I am receiving 0 or 1.
Many thanks in advance

with cte1 as
(
select UserName,ReportName,SUM(reportcount)as reportcount,
RANK() over (partition by reportname order by sum(reportcount) desc) as rank
from View_BIUsers_ReportData where TreeYear = '2011'

group by ReportName,UserName
with cube
)

,CTE2 AS
(
select UserName,ReportName,SUM(reportcount)as reportcount,
RANK() over (partition by reportname order by sum(reportcount) desc) as rank
from View_BIUsers_ReportData where TreeYear = '2011'

group by ReportName,UserName

with cube
HAVING UserName IS NULL
)
SELECT a.UserName,a.ReportName,a.reportcount,b.reportcount,a.rank,a.reportcount/b.reportcount*100
FROM cte1 a inner join CTE2 b
on a.ReportName = b.ReportName

memorykills
Starting Member

18 Posts

Posted - 2011-09-13 : 09:32:42

change a.reportcount/b.reportcount*100 to
a.reportcount*100.0/b.reportcount

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-13 : 09:42:42

Super:) Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources