0

I need to find the each client sum of TotalAmount in Invoice_TBL and sum of TotalPaid in Payment_TBL. The below i have mentioned three tables.

enter image description here

Retrieve query answer should like below image from the above 3 tables. enter image description here

0

2 Answers 2

2

Probably easiest to do with sub-selects:

select clientno
     , coalesce(totalamount,0) as totalamount
     , coalesce(totalpaid,0) as totalpaid
     , least(coalesce(totalamount,0)-coalesce(totalpaid,0),0) as overpaid 
from (
    select c.clientno
        , (select sum(i.totalamount) 
           from invoice i 
           where c.clientno = i.clientno) as totalamount
        , (select sum(p.totalpaid) 
           from payment p 
           where c.clientno = p.clientno) as totalpaid 
    from client c
)

If least is not availible in your DBMS something like:

case when totalamount-totalpaid > 0 
     then 0 
     else totalamount-totalpaid 
end as overpaid

should do

A slightly different approach is to use LATERAL. I think this is called CROSS APPLY in sql-server. I cannot verify this at the moment so you will probably have to modify this:

select clientno
    , coalesce(totalamount,0) as totalamount
    , coalesce(totalpaid,0) as totalpaid
    , case when coalesce(totalamount,0)-coalesce(totalpaid,0) > 0
           then 0 
           else coalesce(totalamount,0)-coalesce(totalpaid,0)
      end as overpaid 
from (
    select c.clientno, i.totalamount, p.totalpaid 
    from client c 
    outer apply ( 
        select sum(totalamount) as totalamount 
        from invoice 
        where clientno = c.clientno 
    ) as i 
    outer apply ( 
        select sum(totalpaid) as totalpaid 
        from payment 
        where clientno = c.clientno 
    ) as p
) as x;
0
1

I'm a fan of CTEs personally. I'm not sure which of these will perform better though so you will need to do some testing.

WITH InvTotal AS (SELECT ClientNo, SUM(TotalAmount) AS TotalAmount
                    FROM Invoice
                  GROUP BY ClientNo),
    PayTotal AS (SELECT ClientNo, SUM(TotalPaid) AS TotalPaid
                    FROM Invoice
                 GROUP BY ClientNo)
SELECT Client.ClientNo, ISNULL(TotalAmount,0) AS TotalAmount, 
        ISNULL(TotalPaid,0) AS TotalPaid, 
        CASE WHEN ISNULL(TotalAmount,0) - ISNULL(TotalPaid,0) > 0 THEN 0
            ELSE ISNULL(TotalAmount,0) - ISNULL(TotalPaid,0) END AS Overpaid
FROM Client
LEFT OUTER JOIN InvTotal
    ON Client.ClientNo = InvTotal.ClientNo
LEFT OUTER JOIN PayTotal
    ON Client.ClientNo = PayTotal.ClientNo;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.