CampaignMax query to populate UWW Churn Spreadsheet

The following query will create the data needed by the churn report:

select * from
(with pledgesbyyear as
(select y.yearsort, p.nameid, sum(p.pledgeamount) as pledgeamount
from campledges p, camappeals a, camyears y
where p.appealid = a.appealid
  and a.yearid = y.yearid
group by y.yearsort, p.nameid),
donorhistory as
(select nameid,
 max(decode(yearsort, '2006', pledgeamount, null)) year5,
 max(decode(yearsort, '2007', pledgeamount, null)) year4,
 max(decode(yearsort, '2008', pledgeamount, null)) year3,
 max(decode(yearsort, '2009', pledgeamount, null)) year2,
 max(decode(yearsort, '2010', pledgeamount, null)) year1
from pledgesbyyear
group by nameid)
select n.DisplayName, n.AddressLine1, n.AddressLine2, n.City, n.State, n.Zip, n.PhoneNumber,
 n.NameID, h.year5, h.year4, h.year3, h.year2, h.year1
from camNamesV n, donorhistory h
where n.NameId = h.NameId
 and (year5 is not null
      or year4 is not null
      or year3 is not null
      or year2 is not null
      or year1 is not null))

To use the query, follow these steps:

1. In CampaignMax, create a new query using the query text above. 
2. Modify the years you want to pull by changing the query.  As shown above, the query will pull 2010 as the most recent year, 2009 as the prior year, etc.
3. Run the query.  This will create a spreadsheet that contains the "raw data" needed by the churn spreadsheet.  Save the spreadsheet.
4. Open the churn spreadsheet template.
5. Go to the Data Entry & Calculations tab
6. Insert columns to the left until the final year label is in column M.  This will ensure that there is enough room in the spreadsheet to paste the raw data from the CampaignMax query.
7. Open the CampaignMax query spreadsheet you created in steps 1-3.  Highlight just the raw campaign data (not the column headings).  Paste this data into the churn spreadsheet at cell A19.
8. Follow the instructions included in the churn spreadsheet.  For example, you need to do the following:
   1. Change the formulas in I6 to M6 so they are "seeing" all of your data.
   2. Enter "5" in K16 so it knows you've imported 5 years of data.
   3. Change the Year Label at M18 to the correct year.