Solved

# sum without dates in query

Posted on 2012-03-20
196 Views
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 ....
0
Question by:Tech_Men
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 8

Expert Comment

ID: 37742453
You can use "WITH CUBE" or "WITH ROLLUP" in the group by clause. They add null value to sum over the grouping fields.
ROLLUP will eliminate rows with NULL in the first grouped column.
0

LVL 69

Expert Comment

ID: 37742456
left outer join (
select LoanKey ,isnull(Sum(LoanSum),0) - ISNULL(sum(PayOnFund),0) as Total
from  dbo.Loans
left join dbo.Pays
on LoanKey=LoanMainKey
where LoanCancelled <> 1
group by LoanKey
) AS totalPays ON
totalPays.LoanKey = L.LoanKey
0

LVL 69

Accepted Solution

Scott Pletcher earned 500 total points
ID: 37742469
Add the subquery above to the main query, something like this:

SELECT
...,
totalPays.Total
from  dbo.Loans L
left outer join (
select LoanKey ,isnull(Sum(LoanSum),0) - ISNULL(sum(PayOnFund),0) as Total
from  dbo.Loans
left join dbo.Pays
on LoanKey=LoanMainKey
where LoanCancelled <> 1
group by LoanKey
) AS totalPays ON
totalPays.LoanKey = L.LoanKey
...rest of query same as before...
0

Author Comment

ID: 37746029
i have try your code but its dosent compute and i get a wrong res
this is the code after i change it :

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 outer join (

select LoanKey,LoanSum - ISNULL(sum(p.PayOnFund),0) as totalPays
from dbo.Pays p left join dbo.Loans l
on LoanMainKey=LoanKey
group by LoanKey,LoanSum

) AS totalPays ON
totalPays.LoanKey = L.LoanKey

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 '12/01/2012'+' 23:59:59' AND LoanCancelled <> 1
group by L.LoanSum,C.FullName,L.LoanKey,L.OpenDate,M.MainLoanKey

this is the res :

dana      93             2011-11-27 00:00:00.000      10000.00      2012.66      1097.33      3110.00
simon      91      2011-11-27 00:00:00.000      10000.00      758.70      600.00      1358.70
simon      94      2011-11-27 00:00:00.000      10000.00      558.40      441.598      1000.00

the total for dana is : 3110 its wrong its need to be : 7987.33  because  the sum is 10.000 and payd 2012.66 so 10.000-2012.66 = 7987.34 i am geting 3110.00

if i do this code i get the right res  (look id 93):

select LoanKey,LoanSum - ISNULL(sum(p.PayOnFund),0) as totalPays
from dbo.Pays p left join dbo.Loans l
on LoanMainKey=LoanKey
group by LoanKey,LoanSum

res :
91      9241.30
93      7987.33
94      9441.59

when i combine this whit the first query its give a wrong calculation
0

LVL 69

Expert Comment

ID: 37747621
Hmm, I don't see where you used the value in the subquery calculation in your main query at all.

The calculated column from the inner query is named "totalPays" -- I don't see a reference to that value anywhere in the outer query (the SELECT above it).
0

Author Comment

ID: 37748707
in this :
from  dbo.Loans L

left outer join (

select LoanKey,LoanSum - ISNULL(sum(p.PayOnFund),0) as totalPays
from dbo.Pays p left join dbo.Loans l
on LoanMainKey=LoanKey
group by LoanKey,LoanSum

) AS totalPays ON
totalPays.LoanKey = L.LoanKey
0

Author Closing Comment

ID: 37749001
thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let youÂ use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties â€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to fâ€¦
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philosâ€¦
###### Suggested Courses
Course of the Month4 days, 17 hours left to enroll