Solved

Need assistance with SQL Query

Posted on 2009-07-07
4
173 Views
Last Modified: 2012-05-07
I am using the following query to return some information for a report:

SELECT tblInvoice.ID, tblInvoice.LegacyInvoiceNumber, tblInvoice.DateSubmitted, tblPaymentTerms.Terms, tblInvoiceStatus.Status, tblCompany.Name AS Company, tblEmployee.UserID AS Employee FROM tblCompany INNER JOIN tblInvoice ON tblCompany.ID = tblInvoice.fkCompany INNER JOIN tblPaymentTerms ON tblInvoice.fkPaymentTerms = tblPaymentTerms.ID INNER JOIN tblInvoiceStatus ON tblInvoice.fkInvoiceStatus = tblInvoiceStatus.ID INNER JOIN tblEmployee ON tblInvoice.fkEmployee = tblEmployee.ID WHERE tblInvoice.fkPaymentTerms IN (1, 2, 3, 4) AND tblInvoice.fkInvoiceStatus IN (1, 2, 3, 4, 5, 6, 7) ORDER BY ID DESC

It works as expected.  Now I need to add an additional calculated field to be returned with the query.  That is the "balance due" on each of the invoices (rows) of the preceding query.  This calculation is done in the program that we use with a bunch of functions, but I need to be able to return the value as part of the above query.  Right now here are the queries that are called by the functions to produce the desired result.  The issue that I can't seem to get my brain around is how to correctly calculate the sales tax based on taxable and non-taxable items in "tblInvoice_detail".

' TOTAL PAYMENTS ON THIS INVOICE
SELECT SUM(Amount) AS Total FROM tblInvoicePayment WHERE (fkInvoice = {0})

' SUM OF ALL DETAIL ROWS ON THIS INVOICE
SELECT SUM(Quantity * UnitPrice) AS SubTotal FROM tblInvoiceDetail WHERE (fkInvoice = {0})

' IF THE INVOICE IS NOT TAX EXEMPT THEN CALCULATE SALES TAX ON TAXABLE ITEMS
SELECT SUM(tblInvoiceDetail.Quantity * tblInvoiceDetail.UnitPrice * tblInvoice.SalesTaxRate) AS SubTotal FROM tblInvoiceDetail INNER JOIN tblInvoice ON tblInvoiceDetail.fkInvoice = tblInvoice.ID WHERE (tblInvoice.ID = {0}) AND (tblInvoiceDetail.Taxable = 1)

Is there a way to roll all this up into one query so that I would have a new calculated field "BalanceDue" returned along with the other information in the first query?  Thanks!
0
Comment
Question by:DanLockwood
  • 2
4 Comments
 
LVL 5

Expert Comment

by:rgc6789
Comment Utility
Forgive me for asking, but is there a TotalAmount in tblInvoice so that you don't have to recalculate the total each time?
0
 

Author Comment

by:DanLockwood
Comment Utility
No, the database doesn't store any calculated fields unfortunately.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Something like this maybe:
SELECT tblInvoice.ID, tblInvoice.LegacyInvoiceNumber, tblInvoice.DateSubmitted

, tblPaymentTerms.Terms, tblInvoiceStatus.Status

, tblCompany.Name AS Company, tblEmployee.UserID AS Employee 

, COALESCE(details.SubTotal, 0) - COALESCE(payments.Total, 0) AS BalanceDue

FROM tblCompany 

INNER JOIN tblInvoice ON tblCompany.ID = tblInvoice.fkCompany 

INNER JOIN tblPaymentTerms ON tblInvoice.fkPaymentTerms = tblPaymentTerms.ID 

INNER JOIN tblInvoiceStatus ON tblInvoice.fkInvoiceStatus = tblInvoiceStatus.ID 

INNER JOIN tblEmployee ON tblInvoice.fkEmployee = tblEmployee.ID 

LEFT JOIN ( -- invoice payments

	SELECT fkInvoice, SUM(Amount) AS Total 

	FROM tblInvoicePayment

	GROUP BY fkInvoice

) payments ON payments.fkInvoice = tblInvoice.ID

LEFT JOIN ( -- detail lines

	SELECT d.fkInvoice

        -- if sales tax rate is a decimal, this should give you invoice + sales tax in one statement

        , SUM(d.Quantity * d.UnitPrice * (1 + CASE d.Taxable WHEN 1 THEN i.SalesTaxRate ELSE 0 END)) AS SubTotal 

	FROM tblInvoiceDetail d

	INNER JOIN tblInvoice i ON i.ID = d.fkInvoice

	GROUP BY d.fkInvoice

) details ON details.fkInvoice = tblInvoice.ID

WHERE tblInvoice.fkPaymentTerms IN (1, 2, 3, 4) 

	AND tblInvoice.fkInvoiceStatus IN (1, 2, 3, 4, 5, 6, 7) 

ORDER BY ID DESC

Open in new window

0
 

Author Closing Comment

by:DanLockwood
Comment Utility
Right on the money.  Thanks!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now