How to query for repayment amount on certain months?-CodePudding

I have two tables as described below:

Table contract:

contract_id	total_loan	interest	contract_status
111	150	50	Active
122	400	25	Finished
133	750	0	Inactive
144	550	50	Active

Table ledger:

ledger_id	contract_id	due_date	loan_amount
1jk	111	2021-07-01	25
2pl	111	2021-08-14	75
1bd	111	2021-08-25	50
7mn	122	2021-07-20	100
6gf	122	2021-08-11	150
9kt	122	2021-09-16	75
5sz	122	2021-10-05	75
3am	133	2021-10-18	750
8hw	144	2021-09-22	550

I need a query to calculate the total repayment (sum of loan_amount interest) for each contract id which contract status is not "Inactive", but only for months on August, September and October.

Expected output:

contract_id	total_repayment
111	175
122	325
144	600

CodePudding user response：

If you are using MySQL,then we can use MONTH() to do it

SELECT a.contract_id,(a.interest   b.loan_amount) as total_repayment
FROM 
 contract a JOIN ledger b ON a.contract_id=b.contract_id
WHERE a.contract_status!='Inactive' 
      AND MONTH(b.due_date) IN(8,9,10)

CodePudding user response：

SQL Server Solution

    SELECT c.contact_id, SUM(l.interest)   SUM(loan_amount) AS total_repayment
    FROM ledger as l JOIN contract as c on l.contract_id = c.contract_id
    WHERE c.contract_status NOT LIKE 'inactive'
    AND datename(month, l.due_date) IN ('August', 'September', 'October')
    GROUP BY c.contact_id;

here you can see that I used datename and passed 2 params to it the first param will represent the part of the date you want to extract and the second is the column name.

You could also look at the DateName function