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.
| 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 DatediffFROM 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 PercentageOnTimeFROM( -- your original query here) s[/code] |
 |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2012-11-06 : 05:41:03
|
| Thanks Anita! |
|
|
|
|
|
|
|
|
Calculating a percentage from total results SQL?