CODEWITHSUNDEEP
sqlsql-server
Alvin Quezon
Alvin Quezon

Reputation: 1211

Getting Balance from Subtracting Total Amount from a Number of Balance

I have problem with deducting my balance from a query here is my code:

Query

select [EmployeeID] = emp.EmployeeID,
    [FullName] = emp.FName + ' ' + emp.LName,
    [LoanTypeID] = lt.LoanTypeID,
    [LoanName] = lt.[Description],
    [Salary] = sal.Amount,
    [TotalLoanAmount] = (
        select SUM(lc1.LoanAmount)
        from LoanContract lc1
        inner join LoanType lt1 on lt1.LoanTypeID = lc1.LoanTypeID
        where lt1.LoanTypeID = lt.LoanTypeID
            and lc1.EmployeeID = emp.EmployeeID
        ),
    [PrincipalAmount] = (
        select ISNULL(SUM((CAST(((lc1.LoanAmount) / ((dbo.fnNumberOfYears(CONVERT(varchar(15), LoanDateStart, 101), CONVERT(varchar(15), LoanPaymentDue, 101)) * 12) * 2)) as decimal(18, 2)))), 0)
        from LoanContract lc1
        inner join LoanType lt1 on lt1.LoanTypeID = lc1.LoanTypeID
        where lt1.LoanTypeID = lt.LoanTypeID
            and lc1.EmployeeID = emp.EmployeeID
        ),
    [Interest] = (
        select ISNULL(SUM((CAST((((lt1.InterestRate / 100) * lc1.LoanAmount) / ((dbo.fnNumberOfYears(CONVERT(varchar(15), LoanDateStart, 101), CONVERT(varchar(15), LoanPaymentDue, 101)) * 12) * 2)) as decimal(18, 2)))), 0)
        from LoanContract lc1
        inner join LoanType lt1 on lt.LoanTypeID = lc1.LoanTypeID
        where lt1.LoanTypeID = lt.LoanTypeID
            and lc1.EmployeeID = emp.EmployeeID
        ),
    [Total] = (
        select ISNULL(SUM((CAST(((((lt1.InterestRate / 100) * lc1.LoanAmount) + lc1.LoanAmount) / ((dbo.fnNumberOfYears(CONVERT(varchar(15), LoanDateStart, 101), CONVERT(varchar(15), LoanPaymentDue, 101)) * 12) * 2)) as decimal(18, 2)))), 0)
        from LoanContract lc1
        inner join LoanType lt1 on lt.LoanTypeID = lc1.LoanTypeID
        where lt1.LoanTypeID = lt.LoanTypeID
            and lc1.EmployeeID = emp.EmployeeID
        ),
    [Balance] = (
        select ISNULL(SUM((CAST(((((lt1.InterestRate / 100) * lc1.LoanAmount) + lc1.LoanAmount) / ((dbo.fnNumberOfYears(CONVERT(varchar(15), LoanDateStart, 101), CONVERT(varchar(15), LoanPaymentDue, 101)) * 12) * 2)) as decimal(18, 2)))), 0)
        from LoanContract lc1
        inner join LoanType lt1 on lt.LoanTypeID = lc1.LoanTypeID
        where lt1.LoanTypeID = lt.LoanTypeID
            and lc1.EmployeeID = emp.EmployeeID
        ),
    [PaymentDate] = lp.DateOfPayment
from Employee emp
inner join Salary sal on sal.EmployeeID = emp.EmployeeID
inner join LoanContract lc on lc.EmployeeID = emp.EmployeeID
inner join LoanPayments lp on lp.LoanID = lc.LoanID
left join LoanType lt on lt.LoanTypeID = lc.LoanTypeID
where lc.EmployeeID in (
        select EmployeeID
        from Employee
        where EmployeeID like case when CAST(ISNULL(@EmployeeID, 0) as varchar(15)) <> 0 then CAST(@EmployeeID as varchar(15)) else '' end + '%'
        )
    and lc.LoanTypeID in (
        select LoanTypeID
        from LoanType
        where LoanTypeID like case when CAST(ISNULL(@LoanType, 0) as varchar(15)) <> 0 then CAST(@LoanType as varchar(15)) else '' end + '%'
        )
    and lp.DateOfPayment between case when ISNULL(@DateStart, '') <> '' then CONVERT(varchar(10), CAST(ISNULL(@DateStart, '') as date), 101) else CONVERT(varchar(10), CAST(ISNULL(GETDATE(), '') as date), 101) end and case when ISNULL(@DateEnd, '') <> '' then CONVERT(varchar(10), CAST(ISNULL(@DateEnd, '') as date), 101) else CONVERT(varchar(10), CAST(ISNULL(GETDATE(), '') as date), 101) end
group by lt.[Description],
    emp.FName,
    emp.LName,
    sal.Amount,
    lp.DateOfPayment,
    emp.EmployeeID,
    lc.LoanTypeID,
    lt.LoanTypeID
order by lp.DateOfPayment,
    emp.FName,
    emp.LName,
    lc.LoanTypeID asc

Result enter image description here

What I am trying to achieve here is that I want to get the total Balance column out of how many Total column from specific LoanTypeID.

For example: LoanTypeID = 1, Balance = 6250, TotalLoanAmount = 150,000 since there are 3 LoanTypeID = 1 then Balance = 18750 and then finally deducted to the TotalLoanAmount so the Balance will be 131,250.

How to achieve this? Can someone help me with this? The only problem is at the Balance column.

Upvotes: 1

Views: 97

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

This should bring you close to what you need. 

SELECT
  EP.*,
  LT.Loan_ID,
  LT.Loan_Amount,
  (SELECT
    SUM(amount)
  FROM LoanPayments LP
  WHERE LP.emp_id = EP.emp_id
  AND LP.Loan_id = LT.Load_id)
  [PaymentsPerLoanAccount],
  LT.Loan_Amount - (SELECT
    SUM(amount)
  FROM LoanPayments LP
  WHERE LP.emp_id = EP.emp_id
  AND LP.Loan_id = LT.Load_id)
  [LoanAccountBalance]

FROM employee EP
LEFT OUTER JOIN LoanTable LT
  ON   -- display all employees regardless if there is a loan
  LT.emp_id = EP.emp_id

Upvotes: 1

Related Questions