Personal Campaign Page Report

Submitted by Robert Printz in Code Sprint
=========================================
SELECT
pcp.id as PcpId,
MAX(pcp.title) as PcpTitle,
MAX(cp.title) as CampaignTitle,
MAX(contact.display_name) as DisplayName,
MAX(pcp.goal_amount) as GoalAmount,
SUM(cs.amount) as TotalCommitted,
(
SELECT SUM(cs2.amount)
FROM civicrm_pcp pcp2
INNER JOIN civicrm_contribution_soft cs2 on cs2.pcp_id = pcp2.id
INNER JOIN civicrm_contribution c2 on c2.id = cs2.contribution_id
WHERE pcp2.id = pcp.id
AND c2.contribution_status_id = 1
) as TotalReceived,
COUNT(1) as NumberOfDonors,
MAX(c.receive_date) as MostRecentDonation
FROM civicrm_pcp pcp
LEFT OUTER JOIN civicrm_contribution_soft cs on cs.pcp_id = pcp.id
LEFT OUTER JOIN civicrm_contribution c on c.id = cs.contribution_id
LEFT OUTER JOIN civicrm_contact contact on contact.id = pcp.contact_id
LEFT OUTER JOIN civicrm_contribution_page cp on cp.id = pcp.contribution_page_id
WHERE (c.contribution_status_id is null or c.contribution_status_id IN (1,2) )
group by pcp.id