Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL query to generate table ordered by Balance

Posted on 2004-09-14
10
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

618 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