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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Stop rounding
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

bombdrop
Starting Member

United Kingdom
5 Posts

Posted - 10/03/2006 :  06:10:23  Show Profile
Im trying to show the percentage breackdown of error stages for a report i'm writting. Problem is it seems to be rounding the percentage how do i stop this many thanks.


declare @ClaimCount int

set @ClaimCount =( select count(*) from claim )

      
 
select
	--Total Claims
	( select count(*) from claim )'Total Claims'
	--AppointmentStage
	,(select count(cause6ref) from claim where cause6ref=1)'Appointment Stage'
	--During Site Inverstigation/Survey
	,(select count(cause6ref) from claim where cause6ref=2)'During Site Inverstigation/Survey'
		--Design Stage
	,(select count(cause6ref) from claim where cause6ref=3)'Design Stage'
	--During Construction
	,(select count(cause6ref) from claim where cause6ref=4)'During Construction'
		--Others
	,(select count(cause6ref) from claim where cause6ref=50)'Others'
	--Non
	,(select count(cause6ref) from claim where cause6ref=0)'Non'



union
	select
	--Total Claims
	(((select count(*) from claim )*100)/ @ClaimCount) 'Total Claims'
	--AppointmentStage
	,(((select count(cause6ref) from claim where cause6ref=1)*100)/ @ClaimCount)  'Appointment Stage'
	--During Site Inverstigation/Survey
	,(((select count(cause6ref) from claim where cause6ref=2)*100)/ @ClaimCount)'During Site Inverstigation/Survey'
		--Design Stage
	,(((select count(cause6ref) from claim where cause6ref=3)*100)/ @ClaimCount)'Design Stage'
	--During Construction
	,(((select count(cause6ref) from claim where cause6ref=4)*100)/ @ClaimCount)'During Construction'
		--Others
	,(((select count(cause6ref) from claim where cause6ref=50)*100)/ @ClaimCount)'Others'
	--Non
	,(((select count(cause6ref) from claim where cause6ref=0)*100)/ @ClaimCount)'Non'


khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 10/03/2006 :  06:14:40  Show Profile
multiply by 100.0 instead of 100


KH

Go to Top of Page

bombdrop
Starting Member

United Kingdom
5 Posts

Posted - 10/03/2006 :  06:27:55  Show Profile
Thank you v.much
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 10/03/2006 :  06:44:22  Show Profile  Visit SwePeso's Homepage
All the 14 subqueries must kill the performance. Try this query and compare speed
SELECT	1.0 * COUNT(*) 'Total Claims',
	1.0 * SUM(CASE WHEN cause6ref = 1 THEN 1 ELSE 0 END) 'Appointment Stage',
	1.0 * SUM(CASE WHEN cause6ref = 2 THEN 1 ELSE 0 END) 'During Site Inverstigation/Survey',
	1.0 * SUM(CASE WHEN cause6ref = 3 THEN 1 ELSE 0 END) 'Design Stage',
	1.0 * SUM(CASE WHEN cause6ref = 4 THEN 1 ELSE 0 END) 'During Construction',
	1.0 * SUM(CASE WHEN cause6ref = 50 THEN 1 ELSE 0 END) 'Others',
	1.0 * SUM(CASE WHEN cause6ref = 0 THEN 1 ELSE 0 END) 'Non'
FROM	Claim
UNION ALL
SELECT	100.0 'Total Claims',
	100.0 * SUM(CASE WHEN cause6ref = 1 THEN 1 ELSE 0 END) / COUNT(*) 'Appointment Stage',
	100.0 * SUM(CASE WHEN cause6ref = 2 THEN 1 ELSE 0 END) / COUNT(*) 'During Site Inverstigation/Survey',
	100.0 * SUM(CASE WHEN cause6ref = 3 THEN 1 ELSE 0 END) / COUNT(*) 'Design Stage',
	100.0 * SUM(CASE WHEN cause6ref = 4 THEN 1 ELSE 0 END) / COUNT(*) 'During Construction',
	100.0 * SUM(CASE WHEN cause6ref = 50 THEN 1 ELSE 0 END) / COUNT(*) 'Others',
	100.0 * SUM(CASE WHEN cause6ref = 0 THEN 1 ELSE 0 END) / COUNT(*) 'Non'
FROM	Claim


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 10/03/2006 :  08:45:19  Show Profile  Send madhivanan a Yahoo! Message
For more informations read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 10/03/2006 :  09:26:40  Show Profile
How about this :


SELECT	a * COUNT(*) 'Total Claims',
	a * SUM(CASE WHEN cause6ref =  1 THEN 1 ELSE 0 END) 'Appointment Stage',
	a * SUM(CASE WHEN cause6ref =  2 THEN 1 ELSE 0 END) 'During Site Inverstigation/Survey',
	a * SUM(CASE WHEN cause6ref =  3 THEN 1 ELSE 0 END) 'Design Stage',
	a * SUM(CASE WHEN cause6ref =  4 THEN 1 ELSE 0 END) 'During Construction',
	a * SUM(CASE WHEN cause6ref = 50 THEN 1 ELSE 0 END) 'Others',
	a * SUM(CASE WHEN cause6ref =  0 THEN 1 ELSE 0 END) 'Non'
FROM	Claim cross join 
        (select a = 1.0 union all select 100.0) b
group by a



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 10/03/2006 :  09:30:39  Show Profile  Visit SwePeso's Homepage
No.
What about the percentage when multiplying with 100?
Maybe, if you change
FROM	Claim cross join 
        (select a = 1.0 union all select 100.0) b
to
FROM	Claim cross join 
        (select a = 1.0 union all select 100.0 / (select count(*) from claim)) b

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/03/2006 09:32:16
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 10/03/2006 :  10:04:27  Show Profile
quote:
Originally posted by Peso

No.
What about the percentage when multiplying with 100?
Maybe, if you change
FROM	Claim cross join 
        (select a = 1.0 union all select 100.0) b
to
FROM	Claim cross join 
        (select a = 1.0 union all select 100.0 / (select count(*) from claim)) b

Peter Larsson
Helsingborg, Sweden


missed that part


KH

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000