building a report - problem with nulls in math

so i have two tables that looks something like so:
CREATE TABLE transactions (
transactionumber INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
transactionamount MONEY,
transactiondate DATETIME
)
go
CREATE TABLE credits (
creditnumber INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
transactionnumber INT, -- this has an FK constraint to the PK of
transactions
creditamount MONEY,
creditdate DATETIME
)
now if i want to run a report that summarizes the amount of
transactions in a given time frame, i might say something like this:
SELECT SUM(t.transactionamount)
FROM transactions AS t
WHERE t.transactiondate > @.startdate AND t.transactiondate < @.enddate
however, that won't take into account the possible credits that were
applied to the transactions, which should be deducted. so i might do
something like this:
SELECT SUM(t.transactionamount) - SUM(c.creditamount)
FROM transactions AS t
LEFT JOIN credits AS c ON t.transactionnumber = c.transactionnumber
WHERE t.transactiondate > @.startdate AND t.transactiondate < @.enddate
which would work fine, except that credits are the exception, so most
of the time, the creditamount produced by the join is NULL, so the
attempt to SUM and subtract it produces an error.
how might i work around this? with a CASE statement? or do i have to do
the report math not in the query (where it would be super fast) but in
the data-consuming application (where it would be super slow)?
thanks for any help,
jasonSELECT SUM(Isnull(t.transactionamount),0) - SUM(Isnull(c.creditamount)
,0)
FROM transactions AS t
LEFT JOIN credits AS c ON t.transactionnumber = c.transactionnumber
WHERE t.transactiondate > @.startdate AND t.transactiondate < @.enddate
Madhivanan|||Thanks, that did the trick