Inner Select Statement gibing wrong result RSS

• • Reply
  jsshivalik

  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' )

  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

  Thanks

• • Reply
  samwu

  Contributor

  3370 Points

  1409 Posts

  Re: Inner Select Statement gibing wrong result

  Mar 24, 2020 02:48 AM|samwu|LINK

  Hi jsshivalik,

  jsshivalik

  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 >