Q

Combining two SQL statements and showing result as percentage

I have taken over a stalled project and now I am struggling to understand SQL while developing a Web application. The problem is the method of combining result sets has eluded me. I am pulling my hair out trying to work it out. Basically, I have two select statements that I need to combine. I have a statement for 'Actual Fees Billed':
 SELECT SUM(ld_period1) as July FROM LS_Lawyer_Data WHERE (ld_year='2004') and (ld_type=1) AND ld_status='A' AND (ld_lawyerid='::ly_id::')
And another statement for 'Potential fees':
 SELECT SUM((LT_TIME.tm_Units/12)* (LD_Lawyer.ly_stdrate*12) ) as fees FROM LT_TIME, ld_lawyer WHERE (LD_lawyer.ly_id=LT_TIME.tm_lyid) AND (LT_TIME.tm_lyid='::ly_id::') AND (LD_lawyer.ly_id='::ly_id::') AND (LT_TIME.tm_Date >= '7/01/2003') AND (LT_TIME.tm_Date <= '7/31/2003') AND (LT_TIME.tm_Units > 0) AND (LT_TIME.tm_Amount <> 0) AND (LT_TIME.tm_txtype=1) AND (LT_TIME.tm_status<>2) Group by LD_Lawyer.ly_stdrate
I have to divide the results of the first one by the results of the second one... then multiply it by 100 and show it as a percentage. I don't know the basic technique of combining results let alone how to write the appropriate query in this instance. Any assistance will be greatly appreciated.

Try:

 Select (ResultA.July/ResultB.fees) * 100 From (SELECT SUM(ld_period1) as July FROM LS_Lawyer_Data WHERE (ld_year='2004') and (ld_type=1) AND ld_status='A' AND (ld_lawyerid='::ly_id::')) AS ResultA, (SELECT SUM((LT_TIME.tm_Units/12)* (LD_Lawyer.ly_stdrate*12) ) as fees FROM LT_TIME, ld_lawyer WHERE (LD_lawyer.ly_id=LT_TIME.tm_lyid) AND (LT_TIME.tm_lyid='::ly_id::') AND (LD_lawyer.ly_id='::ly_id::') AND (LT_TIME.tm_Date >= '7/01/2003') AND (LT_TIME.tm_Date <= '7/31/2003') AND (LT_TIME.tm_Units > 0) AND (LT_TIME.tm_Amount <> 0) AND (LT_TIME.tm_txtype=1) AND (LT_TIME.tm_status<>2) Group by LD_Lawyer.ly_stdrate) AS ResultB

 

For More Information

This was first published in March 2004

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close