Solved

SQL query to generate table ordered by Balance

Posted on 2004-09-14
10
483 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 39
Searching a barcode number within a string. 7 27
Split string into 3 separate fields 5 22
SQL Syntax 6 32
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

733 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