Solved

# sum without dates in query

Posted on 2012-03-20

hi

this is my code :

select C.FullName,L.LoanKey,L.OpenDate,L.LoanSum,

SUM(P.PayOnFund) as OnKerenPay,

isnull(SUM(P.PayOnInterest),0) + ISNULL(sum(PayFines),0) as OnInterst,

isnull(Sum(P.PayOnFund),0)+ISNULL(sum(P.PayOnInterest),0) + ISNULL(sum(PayFines),0) as TotalPay

from dbo.Loans L

left join

dbo.LoanMoves M

on L.LoanKey=M.MainLoanKey

inner join dbo.Customers C on L.CustNum=C.CustomerKey

left join dbo.Pays P on MoveKey=LoanMoveRunId

where P.PayDate between '1/1/2010'+' 00:00:00' And '1/12/2015'+' 23:59:59' AND LoanCancelled <> 1

group by L.LoanSum,C.FullName,L.LoanKey,L.OpenDate,M.MainLoanKey

i need also the get the total sum from the query

i need to combine this code :

select LoanKey ,isnull(Sum(LoanSum),0) - ISNULL(sum(PayOnFund),0) as Total

from dbo.Loans

left join dbo.Pays

on LoanKey=LoanMainKey

group by LoanKey

to get the total pay for each loan row

without consider the date range in the main query

how can i do it ?

thanks ....