Query lists the following columns: donor first name, donor last name, CY sponsor name, CY pledge date, CY pledgetype, CY paidvia, CY pledgeamount, LY pledgeaggregate. There is one row per current year pledge. The pledgeaggregate column is the total of all pledges for the given year.
In this sample, the CY (current year) was set to '2008' and the LY
(last year) was set to '2007'. To change the years included in the
query, change these values to the values used in the year description
field.
select peo.firstname,
peo.lastname,
snam.displayname as sponsor,
pc.pledgedate,
pt.pledgetypedesc,
DECODE(pc.pledgetype,
'P','Payroll Deduction',
'R', 'Credit Card',
'E', 'EFT',
'D', 'Direct Bill',
'C', 'Cash',
'K', 'Check',
'Unknown') as PaidVia,
pc.pledgeamount as pledgeamount2008,
nvl(pl.totalpledge2007,0) as totalpledge2007
from campledges pc,
campeople peo,
camsponsors spon,
camnamesv snam,
camappeals ap,
camyears yr,
campledgetypes pt,
(select nameid, sum(pledgeamount) as totalpledge2007
from campledges p, camappeals a, camyears y
where p.appealid = a.appealid
and a.yearid = y.yearid
and y.yeardesc = '2007'
group by nameid) pl
where pc.nameid = peo.nameid
and pc.sponsorid = spon.sponsorid (+)
and spon.nameid = snam.nameid (+)
and pc.appealid = ap.appealid
and ap.yearid = yr.yearid
and yr.yeardesc = '2008'
and pc.pledgetypeid = pt.pledgetypeid
and pc.nameid = pl.nameid (+)