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
 General SQL Server Forums
 New to SQL Server Programming
 Calculating a percentage from total results SQL?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 11/05/2012 :  10:09:14  Show Profile  Reply with Quote
Hi all,

I am trying to show a percentage of total payments that were in Time from the total results returned when running the following SQL query:

SELECT
job.job_number,
claim_job.contract_code,
claim_job.claim_code,
job.actual_comp_date,
claim_header.claim_date,
(CASE
WHEN trunc(job.actual_comp_date,'DD') - trunc(claim_header.claim_date,'DD') >=28 THEN 'YES'
WHEN trunc(job.actual_comp_date,'DD') - trunc(claim_header.claim_date,'DD') < 27 THEN 'NO'
ELSE 'Unknown'
END) as Late,
trunc(job.actual_comp_date,'DD') - trunc(claim_header.claim_date,'DD') as Datediff
FROM
job
LEFT OUTER JOIN
claim_job
ON
job.contract_code = claim_job.contract_code AND
job.order_number = claim_job.order_number AND
job.job_number = claim_job.order_job_number
LEFT OUTER JOIN
claim_header
ON
claim_header.contract_code = claim_job.contract_code AND
claim_header.claim_code = claim_job.claim_code


-------------------------------------

The identifier if a job is in time is the bold bit of the expression above.

When this query is run the SQL could return an unknown number of results so I am trying to figure out how to get the percentage of total results where the payment was in time (i.e. where Late = No).

Does anyone know how?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  11:49:13  Show Profile  Reply with Quote
SELECT
	100 * SUM( CASE WHEN Late = 'NO' THEN 1 ELSE 0 END)/SUM(1) AS PercentageOnTime
FROM
(
	-- your original query here
) s
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 11/06/2012 :  05:41:03  Show Profile  Reply with Quote
Thanks Anita!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000