Help needed on sum query

i have two table, one containing banking files header (a) information and a
second table containing the banking transactions (b) they are linked by a
LedgerKey field
The banking transaction my have a trans code of either 99 0r 17. Im trying
to get the sum of the header files where transaction trans code = 99, as i
need to knw what the client credit is. There can be several Header records,
but none of the transaction codes are mixed ie one file header will only
have transcode 99 or transcode 17
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from Table1 a
JOIN Table2 b
on a.LedgerKey = b.LedgerKey
where a.Licence = 123456
AND b.TransCode = '99'
a.TransValue varchar(11)
a.LedgerKey Varchar(12)
a.Licence Varchar(6)
b.LedgerKey varchar(12)
b.TransCode varchar (2)
in this instance there are 7 records in table b and im getting a SUM result
of 7 x a.TransValue instread if 1 X a.TransValue
Im a bit reluctant to add the ddl as it wil take a few hours to create
sample date, as for ovious reasons i can not post up live account details.Peter Newman wrote:
> i have two table, one containing banking files header (a)
> information and a second table containing the banking transactions
> (b) they are linked by a LedgerKey field
> The banking transaction my have a trans code of either 99 0r 17. Im
> trying to get the sum of the header files where transaction trans
> code = 99, as i need to knw what the client credit is. There can be
> several Header records, but none of the transaction codes are mixed
> ie one file header will only have transcode 99 or transcode 17
> Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
> from Table1 a
> JOIN Table2 b
> on a.LedgerKey = b.LedgerKey
> where a.Licence = 123456
> AND b.TransCode = '99'
> a.TransValue varchar(11)
> a.LedgerKey Varchar(12)
> a.Licence Varchar(6)
> b.LedgerKey varchar(12)
> b.TransCode varchar (2)
> in this instance there are 7 records in table b and im getting a SUM
> result of 7 x a.TransValue instread if 1 X a.TransValue
> Im a bit reluctant to add the ddl as it wil take a few hours to create
> sample date, as for ovious reasons i can not post up live account
> details.
A few hours? It took me five minutes to simulate your situation:
LedgerKey Varchar(12),
TransValue varchar(11),
Licence Varchar(6))
create table #trans (
LedgerKey varchar(12),
TransCode varchar (2))
insert into #header
select 'abc','1000.00','123456'
insert into #trans
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
go
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from #header a
WHERE EXISTS (Select * FROM #trans b
where a.LedgerKey = b.LedgerKey and
a.Licence = 123456
AND b.TransCode = '99')
drop table #header
drop table #trans
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"