SQL Server Forums
Profile | Register | 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
17635 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
30242 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
22755 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
17635 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
30242 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
17635 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.08 seconds. Powered By: Snitz Forums 2000