Solved

sum without dates in query

Posted on 2012-03-20
7
192 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
  • 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 69

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 69

Accepted Solution

by:
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the 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 69

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Max Consumption Rate (MCR) 3 50
Generate Scripts of Schema/Data with "WHERE" clause 6 60
MS SQL Delete Query 9 30
SSRS 2013 - Creating a summarized report 19 32
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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