I have below cod but when i write this statement then it shows Balance = 0 in all records
(Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
where a2.Account = T1.Account
and a2.RefDate = '2019/03/31' )
Select T1.Account,(Select Sum(A0.Debit) - Sum(A0.Credit) from jdt1 A0 inner join ojdt a1 on A0.transid = A1.transid
where a0.Account = T1.Account and a0.RefDate <= '2019/03/31'
) - (Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
where a2.Account = T1.Account
and a2.RefDate = '2019/03/31' )
as Balance
from Oact T0
inner join JDT1 T1 on T0.AcctCode = T1.Account
inner join OJDT T2 on T1.TransId = T2.TransId
where And T2.RefDate <= '2019/03/31'
group by T1.Account
Select T1.Account,(Select Sum(A0.Debit) - Sum(A0.Credit) from jdt1 A0 inner join ojdt a1 on A0.transid = A1.transid
where a0.Account = T1.Account and a0.RefDate <= '2019/03/31'
) - (Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
where a2.Account = T1.Account
and a2.RefDate = '2019/03/31' )
as Balance
from Oact T0
inner join JDT1 T1 on T0.AcctCode = T1.Account
inner join OJDT T2 on T1.TransId = T2.TransId
where And T2.RefDate <= '2019/03/31'
group by T1.Account
Based on the code you posted, I cannot reproduce your problem.
Please provide me the structure and sample data of all your tables.
Best regards,
Sam
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
140 Points
515 Posts
Inner Select Statement gibing wrong result
Mar 23, 2020 05:20 PM|jsshivalik|LINK
Hi
I have below cod but when i write this statement then it shows Balance = 0 in all records
(Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
where a2.Account = T1.Account
and a2.RefDate = '2019/03/31' )
Thanks
Contributor
3370 Points
1409 Posts
Re: Inner Select Statement gibing wrong result
Mar 24, 2020 02:48 AM|samwu|LINK
Hi jsshivalik,
Based on the code you posted, I cannot reproduce your problem.
Please provide me the structure and sample data of all your tables.
Best regards,
Sam