Solved

Need assistance with SQL Query

Posted on 2009-07-07
4
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Expert Comment

by:rgc6789
ID: 24795658
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
ID: 24795694
No, the database doesn't store any calculated fields unfortunately.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24795793
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
ID: 31600669
Right on the money.  Thanks!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

626 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