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
 General SQL Server Forums
 New to SQL Server Programming
 Calculating a percentage from total results SQL?
 New Topic  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  
 New 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.14 seconds. Powered By: Snitz Forums 2000