SQL Grantedge Donor Giving History by Field of Interest

SQL Grantedge Donor Giving History by Field of Interest

SELECT     

Grants.GrantId, Grants.PayDate AS GrantDate, Grants.FundNumber, Grants.FundName, Grants.RecipientName, Grants.PayeeName, Grants.GrantType, 
Grants.GrantStatus, Grants.PaymentAmount AS GrantAmount, Payments.PayDate AS DatePaid, Payments.TotalAmount AS PaymentAmount, Payments.PayStatus, 
Payments.CheckNumber, Payments.CheckDate, Payments.AdjustmentAmount, TL.TopLevelProgramArea, Grants.RecipientCity, WS.Email, 

(CASE WHEN Grants.PayDate >= '20050701' AND Grants.PayDate < '20060630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2006, 
(CASE WHEN Grants.PayDate >= '20060701' AND Grants.PayDate < '20070630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2007, 
(CASE WHEN Grants.PayDate >= '20070701' AND Grants.PayDate < '20080630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2008, 
(CASE WHEN Grants.PayDate >= '20080701' AND Grants.PayDate < '20090630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2009, 
(CASE WHEN Grants.PayDate >= '20090701' AND Grants.PayDate < '20100630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2010, 
(CASE WHEN Grants.PayDate >= '20100701' AND Grants.PayDate < '20110630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2011, 
(CASE WHEN Grants.PayDate >= '20110701' AND Grants.PayDate < '20120630' THEN Grants.PaymentAmount ELSE 0 END) AS FY2012, 
(CASE WHEN Grants.PayDate < '20050701' THEN Grants.PaymentAmount ELSE 0 END) AS Older

FROM         
V_GRRPT_Grants AS Grants INNER JOIN
V_GRRPT_PAYMENTS AS Payments ON Payments.GrantId = Grants.GrantId INNER JOIN
V_GRQRY_GrantCharacteristics AS GrantChara ON GrantChara.GrantId = Payments.GrantId INNER JOIN
GRGrantCharacteristics AS GRGrantChara ON GRGrantChara.GrantId = GrantChara.GrantId INNER JOIN
GRCharacteristicCodes AS GRCode ON GRCode.CharacteristicId = GRGrantChara.CharacteristicId INNER JOIN
GRCharacteristics AS GRCh ON GRCode.CharacteristicId = GRCh.CharacteristicId LEFT OUTER JOIN
V_Coastal_Constituent_Website AS WS ON Payments.GrantId = WS.GrantId LEFT OUTER JOIN
V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM AS TL ON GrantChara.GrantId = TL.GrantId

WHERE     (Grants.FundNumber = @FundNumber)

GROUP BY 
Grants.GrantId, Grants.PayDate, Grants.FundNumber, Grants.FundName, Grants.RecipientName, Grants.PayeeName, Grants.GrantType, Grants.GrantStatus, 
Grants.PaymentAmount, Payments.PayDate, Payments.TotalAmount, Payments.PayStatus, Payments.CheckNumber, Payments.CheckDate, 
Payments.AdjustmentAmount, TL.TopLevelProgramArea, Grants.RecipientCity, WS.Email

HAVING      (Grants.GrantType <> 't')

ORDER BY Grants.RecipientName, TL.TopLevelProgramArea

Select FundNumber

TopLevelProgram Area pulls Level 1 of Characteristics