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
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.
Our community of experts have been thoroughly vetted for their expertise and industry experience.