troubleshooting Question

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

Avatar of Jeremy Poisson
Jeremy PoissonFlag for United States of America asked on
SQL
5 Comments1 Solution80 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
James0628

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros