?
Solved

SQL query to generate table ordered by Balance

Posted on 2004-09-14
10
Medium Priority
?
490 Views
Last Modified: 2008-03-06
I'm working with SQL server and ASP...

I have 3 tables:
-Clients (contains CLIENT_ID)
-Invoices (contains INVOICE_ID and INVOICE_AMOUNT fields, and is related to Clients with CLIENT_ID field)
-Payments (contains PAYMENT_ID and PAYMENT_AMOUNT fields, and is related to Invoices with INVOICE_ID field)

I'm trying to build a query to generate a table with the next columns...
Client_ID     Total_Invoices_Amount       Total_Payments_Amount      Balance
----------      ---------------------------      -----------------------------      ---------

Table must be grouped by Client_ID.
Total_Invoices_Amount field = sums all amounts of invoices made for each client.
Total_Payments_Amount = sums all amounts of payments made by each client.
Balance = Difference of Total invoices and Total payments (Total_Invoices_Amount - Total_Payments_Amount).
Table must be ORDERED BY BALANCE FIELD desc.

This must be all contained into the same query to make possible ORDER BY Balance.
I have traied a lot of queries w/o success. Just wrong calculations.
0
Comment
Question by:egwerta
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12055053
Try:

Select a.client_ID, sum(b.INVOICE_AMOUNT), sum(c.PAYMENT_AMOUNT), sum(c.PAYMENT_AMOUNT)-sum(b.INVOICE_AMOUNT) as Balance
from clients a, invoices b, payments c
where a.client_ID = b.CLIENT_ID
and b.INVOICE_ID = c.INVOICE_ID
group by a.Client_ID
order by sum(c.PAYMENT_AMOUNT)-sum(b.INVOICE_AMOUNT)
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12055076
Try this:

SELECT c.Client_ID, Total_Invoice_Amount, Total_Payment_Amount,
 Total_Invoice_Amount - Total_Payment_Amount as Balance
FROM (--get invoice totals
   SELECT c.Client_ID, Sum(i.Invoice_Amount) Total_Invoice_Amount
   FROM Clients c INNER JOIN Invoices i ON c.Client_ID = i.Client_ID
   GROUP BY Client_ID
) i INNER JOIN (--get payment totals
   SELECT c.Client_ID, Sum(p.Payment_Amount) Total_Payment_Amount
   FROM Clients c INNER JOIN Payments p ON c.Client_ID = p.Client_ID
   GROUP BY c.Client_ID
) p ON i.Client_ID = p.Client_ID
ORDER BY 4 DESC
0
 
LVL 19

Expert Comment

by:peh803
ID: 12055090
Okay, don't laugh --- here goes nothing...

SELECT
  C.Client_ID,
  (
    SELECT SUM (I.Invoice_Amount) FROM Invoices I WHERE I.Client_ID = C.Client_ID
  ) AS Total_Invoices_Amount,
  (SELECT SUM (P.Payment_Amount) FROM Payments P WHERE Invoice_ID IN
      (SELECT Invoice_ID FROM Invoices In WHERE In.Client_ID=C.Client_ID)
  ) AS Total_Payment_Amount,
  (
    (SELECT SUM (I.Invoice_Amount) FROM Invoices I WHERE I.Client_ID = C.Client_ID) -
    (SELECT SUM (P.Payment_Amount) FROM Payments P WHERE Invoice_ID IN
      (SELECT Invoice_ID FROM Invoices In WHERE In.Client_ID=C.Client_ID))
  ) AS Balance
FROM Clients
ORDER BY
  ((SELECT SUM (I.Invoice_Amount) FROM Invoices I WHERE I.Client_ID = C.Client_ID) -
    (SELECT SUM (P.Payment_Amount) FROM Payments P WHERE Invoice_ID IN
      (SELECT Invoice_ID FROM Invoices In WHERE In.Client_ID=C.Client_ID)))

peh803
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 26

Expert Comment

by:Hilaire
ID: 12055117
Can you give it a try ?

Select a.Client_ID,
SUM(a.INVOICE_AMOUNT) as Total_Invoices_Amount,
SUM(b.PAYMENT_AMOUNT ) as Total_Payments_Amount,
SUM(a.INVOICE_AMOUNT) - COALESCE(SUM(b.PAYMENT_AMOUNT ), 0) as Balance
From Invoices a LEFT OUTER  JOIN Payments b on a.INVOICE_ID = b.INVOICE_ID
ORDERED BY 4 desc

Cheers

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12055140
Oops, missing group by + Typo
The left outer join is important to keep the invoices when no payment avail

Select a.Client_ID,
SUM(a.INVOICE_AMOUNT) as Total_Invoices_Amount,
SUM(b.PAYMENT_AMOUNT ) as Total_Payments_Amount,
SUM(a.INVOICE_AMOUNT) - COALESCE(SUM(b.PAYMENT_AMOUNT ), 0) as Balance
From Invoices a LEFT OUTER  JOIN Payments b on a.INVOICE_ID = b.INVOICE_ID
GROUP BY a.Client_ID
ORDER BY 4 desc
0
 
LVL 4

Accepted Solution

by:
xxg4813 earned 1500 total points
ID: 12068365
Try this one, it should work

---------------------------------------------------------------------------------------

select c.client_id,
       isnull(i.invoice_amount,0) Total_Invoices_Amount,
       isnull(p.payment_aount,0) Total_Payments_Amount,
       Total_Invoices_Amount - Total_Payments_Amount Balance
from clients c
left outer join (select client_id, sum(invoice_amount) invoice_amount
                 from invoices
                 group by invoice_id) i on c.client_id = i.client_id
left outer join (select c.client_id, sum(p.payment_amount) payment_amount
                 from clients c, invoices i, payments p
                 where c.client_id = i.client_id
                   and i.invoice_id = p.invoice_id
                 group by c.client_id) p on c.client_id = p.client_id

-------------------------------------------------------------------------------------------
0
 
LVL 4

Expert Comment

by:xxg4813
ID: 12068405

forgot the order by balance desc

u can do it by yourself

good luck!
0
 
LVL 19

Expert Comment

by:peh803
ID: 12113359
@egwerta -- is this still an issue for you?  Please let us know so we can help out (if you still need it).  Otherwise, please post any relevant comments and close the question.

Thank you,
peh803
0
 

Author Comment

by:egwerta
ID: 12114618
This was the final working sentence (in spanish)...

SELECT A.Client_ID, B.Sum_Invoices, C.Sum_Payments, (isnull(B.Sum_Invoices,0) - isnull(C.Sum_Payments,0)) AS Balance
FROM (SELECT DISTINCT Client_ID FROM Clients) A
INNER JOIN (SELECT Client_ID, SUM(B2.Unit_Price * B2.Qty) AS Sum_Invoices
FROM Invoices B1 INNER JOIN Invoice_Details B2 ON B1.Invoice_ID = B2.Invoice_ID
GROUP BY Client_ID) B
ON A.Client_ID = B.Client_ID
LEFT JOIN (SELECT Client_ID, SUM(Total_Payment) AS Sum_Payments
FROM Payments GROUP BY Client_ID) C ON A.Client_ID = C.Client_ID
ORDER BY Balance DESC
0
 
LVL 19

Expert Comment

by:peh803
ID: 12114657
Thanks for updating / closing this question -- glad you were able to get a working solution in place.

peh803
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question