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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
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
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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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