Avatar of Jeremy Poisson
Jeremy Poisson
Flag for United States of America asked on

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
SQL

Avatar of undefined
Last Comment
Jeremy Poisson

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
James0628

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mike McCracken

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
Jeremy Poisson

ASKER
GREAT - Thank You!
James0628

You're welcome.  Glad I could help.

 James
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeremy Poisson

ASKER
I might have another one coming up soon!