Solved

SQL query to generate table ordered by Balance

Posted on 2004-09-14
10
478 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

770 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