Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Grouping Master and Child table

Author  Topic 

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-01-27 : 05:44:09
Table 1: SELECT * FROM Loandisbursement - 3 Records (Members)
Table 2: SELECT * FROM Loan_Demand_Transaction - Table 1 Records with Due Date

I need to join this table and have to calculate some Loan Details from Aggregate function. From Table 2 i got the output correct. While joining These 2 tables Table 1 Summation is wrong. Please help me in this.

SELECT LDT.DivisionName,
LDT.BRanchName,
SUM(LD.loanamount) As PrincipleAmt,
isnull(sum(LDT.ap_P),0) AS Recd
FROM Loandisbursement LD
INNER JOIN Loan_Demand_Transaction LDT ON LDT.Centreid=LD.Centreid AND LDT.Memberid=LD.Memberid AND LDT.loancycle=LD.loancycle AND LDT.BranchName=LD.BranchName AND LDT.Centreid=LA.Centreid AND LDT.Memberid=LA.Memberid AND LDT.loancycle=LA.loancycle AND LDT.BranchName=LA.BranchName
WHERE LD.loansanction='Yes' AND LDT.duedate<=CONVERT(datetime,'27/1/2011',103)
AND LDT.Status in (2,3) AND LDT.duedate<=CONVERT(datetime,'27/1/2011',103)
GROUP BY LDT.DivisionName,LDT.BRanchName
ORDER BY LDT.DivisionName,LDT.BRanchName

Output: (For this Query)
PrincipleAmount 60000

Exact Output:
PrincipleAmount 30000
Because 3 records from Table 1 is 10000+10000+10000=30000

But for me output is wrong. Because of joining this 2 tables output is varied. Pleae help me to find the solution.

Regards,
Kalaiselvan R
Love Yourself First....
   

- Advertisement -