[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

sum without dates in query

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
Tech_Men
Asked:
Tech_Men
  • 3
  • 3
1 Solution
 
gpizzutoCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Tech_MenAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Tech_MenAuthor Commented:
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
 
Tech_MenAuthor Commented:
thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now