inner join and left join

I have two tables: ACCOUNTS and TRANSACTIONS and I need to produce a cursor
showing comparative totals for the last two years.

I know how to do a sql to get the totals of transactions for last year, and
the totals of transactions for this year, for each account. However I can't
work out how to join them, because there are some accounts that will have no
transactions against them for last year, some that will only have
transactions against them for last year, and some accounts that will have
transactions in both years.

Is creating two cursors, one for each year, and then attempting to join them
the best way, or is there an easier way?

Many thanks

Stephen

Olaf
Tue Mar 04 10:58:21 CST 2008

You could

select
sum(iif(year(transactiondate)=x),transaction,0) as yearXtotal,
sum(iif(year(transactiondate)=y),transaction,0) as yearYtotal
from transactions
group by accountno
where year(transactiondate) between x and y

to get the totals in a single row.

I guess it's still faster to join two results to the list of accounts.

if yearx and yeary are the two result cursors for year x and year y do:

select accounts.accountno, yearx.total as yearxtotal, yeary.total as
yearytotal;
from accounts;
left join yearx on yearx.accountno = accounts.accountno
left join yeary on yeary.accountno = accounts.accountno
having yearxtotal#0 OR yearytotal#0

Bye, Olaf.

Stephen
Wed Mar 05 07:51:33 CST 2008

Many thanks Olaf - much appreciated

Stephen

"Olaf Doschke" <b2xhZi5kb3NjaGtlQHNldG1pY3MuZGU@strconv.14.de> wrote in
message news:OhuK7jhfIHA.1900@TK2MSFTNGP02.phx.gbl...
> You could
>
> select
> sum(iif(year(transactiondate)=x),transaction,0) as yearXtotal,
> sum(iif(year(transactiondate)=y),transaction,0) as yearYtotal
> from transactions
> group by accountno
> where year(transactiondate) between x and y
>
> to get the totals in a single row.
>
> I guess it's still faster to join two results to the list of accounts.
>
> if yearx and yeary are the two result cursors for year x and year y do:
>
> select accounts.accountno, yearx.total as yearxtotal, yeary.total as
> yearytotal;
> from accounts;
> left join yearx on yearx.accountno = accounts.accountno
> left join yeary on yeary.accountno = accounts.accountno
> having yearxtotal#0 OR yearytotal#0
>
> Bye, Olaf.
>
>
>
>