I'm working on a project, in which I need to calculate all the due payment and to show in DataGridView. I've made a query in Query Design. But I'm unable to make query to get total, paid amount, due amount. Here is the query:
SELECT Payment.PayDate, OrderMaster.OrderId, OrderMaster.CustomerName, OrderMaster.TotalAmount, Payment.PayAmount, OrderMaster.TotalAmount - Payment.PayAmount AS DueAmount FROM OrderMaster INNER JOIN Payment ON OrderMaster.OrderId = Payment.OrderId;
I'm getting the query result like this:
PayDate | OrderId | CustomerName | TotalAmount | PayAmount | DueAmount
-------------------------------------------------------------------------
20/12/2014 | 1 | Someone | 5000 | 2000 | 3000
22/12/2014 | 1 | Someone | 5000 | 1000 | 4000
25/12/2014 | 1 | Someone | 5000 | 2000 | 3000
I want the query result like this:
PayDate | OrderId | CustomerName | TotalAmount | PayAmount | DueAmount
-------------------------------------------------------------------------
20/12/2014 | 1 | Someone | 5000 | 2000 | 3000
22/12/2014 | 1 | Someone | 5000 | 1000 | 2000
25/12/2014 | 1 | Someone | 5000 | 2000 | 0
Please help me to make the exact query to get this result. Thanks.
Aucun commentaire:
Enregistrer un commentaire