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
 Calculating a percentage from total results SQL?

Author  Topic 

Maverick_
Posting Yak Master

107 Posts

Posted - 2012-11-05 : 10:09:14
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
Master Smack Fu Yak Hacker

5155 Posts

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

Maverick_
Posting Yak Master

107 Posts

Posted - 2012-11-06 : 05:41:03
Thanks Anita!
Go to Top of Page
   

- Advertisement -