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