CODEWITHSUNDEEP
sqlsql-server
Rohan
Rohan

Reputation: 177

How to calculate amount against Sum of amount in another table

I am working on project where i need to calculate amount present in one table against payments made from another table

enter image description here

Upvotes: 0

Views: 144

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

If you are on SQL Server 2012+ then you can use window version of SUM with ORDER BY clause, which calculates running total:

SELECT OrderId, o.Customer, Amount,
       CASE 
         WHEN SUM(Amount) OVER (PARTITION BY o.Customer 
                                ORDER BY OrderId) <= p.totalPayment 
         THEN 'Paid'
         ELSE 'Unpaid'
       END AS Status
FROM Orders AS o
LEFT JOIN (
   SELECT Customer, SUM(Amount) AS totalPayment
   FROM Payments
   GROUP By Customer
) AS p ON o.Customer = p.Customer
ORDER BY OrderId

If the running total of the order's amount per customer is covered by the totalPayment amount then 'Paid' is selected as output, otherwise the output is Unpaid.

Upvotes: 1

Albert Hugo
Albert Hugo

Reputation: 114

The payments aren't related to the order id but to the customer. You don't know which order is being paid so you have to assume that the oldest order of the paying customer is being paid first. When deciding whether an order is paid you need to know the total amount paid by the customer and the total amount of all the older orders of this customer. The remaining payment amount is the difference between these two amounts. If the difference is greater or equal than the order amount the order is paid. Otherwise not.

Try this:

select o.orderid
, o.customer
, o.amount
, isnull((select sum(p.amount) 
from paymentstable p 
where p.customer=o.customer),0) as paid_in_total
, isnull((select sum(o2.amount) 
from orderstable o2 
where o2.customer=o.customer 
and o2.orderid<o.orderid),0) as previous_order_amounts
, isnull((select sum(p.amount) 
from paymentstable p 
where p.customer=o.customer),0) - isnull((select sum(o2.amount) 
from orderstable o2 
where o2.customer=o.customer 
and o2.orderid<o.orderid),0) as remaing_payment
, case when isnull((select sum(p.amount) 
from paymentstable p 
where p.customer=o.customer),0) - isnull((select sum(o2.amount) 
from orderstable o2 
where o2.customer=o.customer 
and o2.orderid<o.orderid),0) >= o.amount then
    'paid'
else
    'unpaid'
end status

from orderstable o
order by o.orderid

The columns paid_in_total, previous_order_amounts and remaining_payment can be removed. I left them there in order to show the logical steps.

Albert

Upvotes: 1

Related Questions