December 2, 2008 at 3:45 pm
hi ,
if i use the following query
select tradername, isnull(sum(btty.debit),0)- isnull(sum(btty.credit),0)
as opbalance
from dbo.traders
left outer join dbo.btty
on traders.traderid = btty.traderid
group by tradername
i get the result as this
tradername opbalance
harish 0.00
prakash 225.00
rag 0.00
vedu 0.00
if i use this query i get
select tradername,
isnull(sum(balancetable.debit),0)- isnull(sum(balancetable.credit),0)
as closingbalance
from dbo.traders
left outer join dbo.balancetable
on traders.traderid = balancetable.traderid
group by tradername
tradername closingbalance
harish 0.00
prakash 1025.00
rag 0.00
vedu 0.00
but when i try to join these both queries like
select tradername, isnull(sum(btty.debit),0)- isnull(sum(btty.credit),0)
as opbalance,
isnull(sum(balancetable.debit),0)- isnull(sum(balancetable.credit),0)
as closingbalance
from dbo.traders
left outer join dbo.btty
on traders.traderid = btty.traderid
left outer join dbo.balancetable
on traders.traderid = balancetable.traderid
group by tradername
i get this
tradername opbalance closingbalance
harish 0.00 0.00
prakash 900.00 1025.00
rag 0.00 0.00
vedu 0.00 0.00
i have no idea why the opbalance for prakash is showing as 900
it should be 225 . what am i doing wrong ..
Please help
thanks and regards
bonhasgone
December 2, 2008 at 5:23 pm
The join with balancetable results in more rows than btty table. So when u join all 3 tables in a big join there are duplicate rows which sums the duplicate values gives wrong result.
December 2, 2008 at 5:30 pm
hi ,
thanks for the reply ... i think i get it now .. but can you please tell me how i can fix it..?
thanks and regards
Bonhasgone
December 2, 2008 at 8:17 pm
Like this:
Select tr.tradername
, opbalanace
, closingbalance
From dbo.Traders tr
Left Join (Select traderid
, IsNull(Sum(debit),0)- IsNull(Sum(credit),0) as opbalance
From dbo.btty
Group By traderid) op
ON op.traderid = tr.traderid
Left Join (Select traderid
, isnull(sum(debit),0)- isnull(sum(credit),0) as closingbalance
From dbo.balancetable
Group By traderid) cl
ON cl.traderid = tr.traderid
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply