?
Solved

Need help with the best way to write an SQL query

Posted on 2012-09-05
10
Medium Priority
?
522 Views
Last Modified: 2012-09-05
Hello Experts,

I have written a fairly complex query (with 11 joins) to report on financial activity.  I ended up writing sub queries to return 'Total Orders'; 'Total Invoices'; 'Total Credit Memos'; and 'Total A/R downpayments'

Now that I have returned those 4 fields (along with many columns of supporting data) I have been asked to do some simple math on the 4 fields listed above.  For example, total the Invoices, credit memos and A/R downpayments and display as a column titled 'Total billed'.

The only way I know how to do this is to cut and paste the sub query that creates each existing column and add the result of each sub query.  I would need to do the same thing about 5 more times for other fields that have been requested.

It would look something like this:

(SELECT SUM(Invoices) FROM .......)
+
(SELECT SUM(Credit Memos) FROM ....)
+
(SELECT SUM(A/R Downpayments) FROM...)
AS 'Total Billed'

Open in new window


This seems very cumbersome and very inefficient.  Bear in mind that each sub query is 5 or 6 lines long.  I experimented with assigning the value returned by a sub query to a variable, and that doesn't seem to work.

Can anybody offer a suggestion of a better way to do what I'm trying to do ?

Thanks !!
0
Comment
Question by:Steve5140
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 11

Expert Comment

by:John Easton
ID: 38367692
Not that I've tried it, but if the from / where for each query is the same you should be able to do something like:

SELECT Sum(Invoice) + Sum(Credit Memos) + Sum(A/R Downpayments) AS TotalBilled FROM ....
0
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 38367698
Could you wrap your various chunks of SQL code that calculate the individual figures into a stored procedure, and as each chunk of code is executed to calculate a particular value, write that value into a table?  Once all of the individual values are calculated, you could write an aggregate query as the final part of the stored procedure that adds the values you have stored in the table.

HTH
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38367773
Like armchair_scouse said, probably your best bet would be to create a stored procedure to implement your logic.
You'd create a temp table (or table variable) and insert all your initial data (with subquery results) and empty fields (or filed with 0.00) for your calculation fields.
Once you've done this you can run an update on the temp/variable with the calculations needed.
At the end you'd do a select over the table to return the processed dataset.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:ransommule
ID: 38367843
You could encapsulate each query in a user definded function in order to allow SQL to precompile the query and have a better performance.

DECLARE @varInvoices INT
DECLARE @varMemos INT
DECLARE @varPayments INT

DECLARE @varTotal INT

SET @varInvoices =  dbo.MyInvoiceFunction()
SET @varMemos = dbo.MyCreditMemos()
SET @varPayments = dbo.MyDownpayments()

SET @varTotal  = @varSUM1  + @varMemos + @varPayments
0
 

Author Comment

by:Steve5140
ID: 38367899
Thanks for all the input, that is what I love about this board !!

Couple of comments:

JEaston - each sub query selects from unique, unrelated tables, so I don't think I can combine the subqueries as you suggest

LionKing - You fired an idea that I think may be my best option.  The results of the query are bound to a .net data grid.  What I may do is add some empty fields to the query as you suggest and then run down the data grid and fill in the blanks with the required calculations.  This has the added advantage of moving the subquery processing off the SQL server and onto the client machine.

RansomMule - interesting idea, but as every row is returned by the main query the sub query runs with a new 'Project number' in the WHERE clause.  I don't believe that I can call SET in the main query (maybe I don't understand what you are saying).

If there are no objections I will award the points to LionKing.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38367929
Remember that calculations will be processed faster by the server than by any client.
Most of the time, you'll rather have more data processing on the server side in order to take advantage of the server "power".
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38368459
Put the results of the big, initial query into a temp table.  Then query on that temp table to do the additional SUMs/calculations.


SELECT ...same_as_existing_query...
INTO #temp_save
FROM ...same_as_existing_query...


SELECT
    *,
    (SELECT SUM([Orders]) AS [Total Orders] FROM #temp_save) AS [Total_Orders],
    (SELECT SUM([Credit Memos]) AS [Total Credit Memos] FROM #temp_save) AS [Total Credit Memos],
    ...,
    (SELECT SUM([Orders]) + SUM([Credit Memos]) + SUM([...]) + ... AS [Total Billed]
FROM #temp_save
ORDER BY
    ...
0
 

Author Comment

by:Steve5140
ID: 38368507
Scott,

Per genius !!!  Works like a charm.

Problem is that I already assigned points before I got your answer...

What now ?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38368908
Thanks!

You can ask that the q be reopened so you can adjust the point awards.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question