SQL Sales Query - Showing Invoices and Credits in separate column not working!

Experts,

  Having a bit of a challenge getting the proper data to show (invoices + credits) between 2 different SQL tables. I cannot seem to find a common denominator that would allow me to see individual invoices and credits, in ONE single query. Of course, if I run them separately, all works well.

The first query below shows all invoices - 100% perfect. The second query shows all credits - also 100% perfect. The third query shows my attempt at trying to combine invoices and credits in one query - NOT perfect :(

Can you take a look and let me know what I'm missing?

I've included the queries I'm using, a spreadsheet with results (alone with each query).

Thank you, in advance!!

INVOICES QUERY:

SELECT     Cust.AccountNumber, inv.CustomerID, Cust.Company, SUM(ISNULL(INV.Amount, N'0')) AS Invoice_Amount,
INV.DATE, INV.EditSequence
FROM         dbo.Invoices AS INV INNER JOIN
                      dbo.Customers AS Cust ON INV.CustomerId = Cust.ID
WHERE     (INV.Date BETWEEN DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AND DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) AND
                      (Cust.AccountNumber IS NOT NULL) AND (Cust.AccountNumber <> '')
                      AND (CUST.ACCOUNTNUMBER = '205847')
                     ---AND inv.CustomerID = '80001BD9-1479497768'
GROUP BY Cust.Company, Cust.AccountNumber, INV.DATE, inv.CustomerID, INV.EditSequence
order by  cust.Company


CREDITS QUERY:

SELECT     Cust.AccountNumber, inv.CustomerID, Cust.Company, SUM(ISNULL(INV.Amount, N'0')) AS Invoice_Amount,
INV.DATE, INV.EditSequence
FROM         dbo.Invoices AS INV INNER JOIN
                      dbo.Customers AS Cust ON INV.CustomerId = Cust.ID
WHERE     (INV.Date BETWEEN DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AND DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) AND
                      (Cust.AccountNumber IS NOT NULL) AND (Cust.AccountNumber <> '')
                      AND (CUST.ACCOUNTNUMBER = '205847')
                     ---AND inv.CustomerID = '80001BD9-1479497768'
GROUP BY Cust.Company, Cust.AccountNumber, INV.DATE, inv.CustomerID, INV.EditSequence
order by  cust.Company


COMBINED QUERY:

SELECT     Cust.AccountNumber,  Cust.Company,
SUM(ISNULL(INV.Amount, N'0')) AS Invoice_Amount,
CASE WHEN  Inv.Amount = CM.Amount  then
CM.AMOUNT else '0' end AS Credit_Amount,
INV.DATE, INV.EditSequence
FROM         dbo.Invoices AS INV INNER JOIN
                      dbo.Customers AS Cust ON INV.CustomerId = Cust.ID LEFT OUTER JOIN
                      dbo.CreditMemos AS CM ON cust.ID = CM.CustomerId
WHERE     (INV.Date BETWEEN DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AND DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) AND
                      (Cust.AccountNumber IS NOT NULL) AND (Cust.AccountNumber <> '')
                      AND (CUST.ACCOUNTNUMBER = '205847')                    
GROUP BY Cust.Company, Cust.AccountNumber, INV.DATE, inv.CustomerID, INV.EditSequence, cm.CustomerId, inv.Amount, cm.amount
order by cust.company
QuickBooks-Invoices-Credits.xlsx
Jeremy PoissonPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
The "CREDITS QUERY" in your post is just your invoice query, but the correct(?) query is in the Excel file.

 As for the combined query, I see a few different things to look at.

 You don't check the credit date.  And you only use the customer for the Join, so for every invoice, you're pulling in every credit for that customer.  If the credits are linked to an invoice, then you should be using some invoice columns to find any associated credits.  If they're not linked, then you presumably need to check the credit date.  But then I think you'd get each invoice repeated once for each credit in that date range, so I think you might want to use Customers as the driving table (instead of Invoices, as noted below), with Outer joins to the invoices and credits.

 The query is driven by Invoices.  If a customer had a credit in the desired date range, but no invoices, they would not be included.  Or, as noted above, if a customer had multiple credits, the invoices would be duplicated.

 You only use the credit amount (CM.Amount) when it's the same as Inv.Amount.  That seems odd to me.  Without knowing more about your tables, I can't really say whether that's right or wrong, but it presumably explains (at least in part) why you always get 0 in the results.


 FWIW, if the separate invoice and credit queries produce the same columns, then the simplest way to "combine" the results would be to just Union the two queries, as long as having the invoices and credits in separate rows isn't a problem.  You could put the invoice and credit amounts in different columns if you like, and/or add something like an Invoice_or_Credit column that's set to "Invoice" in one query and "Credit" in the other, so that you know which is which.

 James
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Are you trying to see all customers who have either credits or invoices or only customers who have both?  

If it is the former then you need to join from the customer table to the invoice and credits table

Perhaps the easiest way to do this is with a full outer join of you 2 queries that return the data you want.

mlmcc
0
Jeremy PoissonPresidentAuthor Commented:
GREAT - Thank You!
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Jeremy PoissonPresidentAuthor Commented:
I might have another one coming up soon!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.