?
Solved

sum without dates in query

Posted on 2012-03-20
7
Medium Priority
?
198 Views
Last Modified: 2012-03-21
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
Comment
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
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:gpizzuto
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 70

Expert Comment

by:Scott Pletcher
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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Tech_Men
ID: 37746029
hi thanks for your answer
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 70

Expert Comment

by:Scott Pletcher
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

by:Tech_Men
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

by:Tech_Men
ID: 37749001
thanks
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question