September 21, 2011 at 9:12 am
I hope you're still out there Lutz.... I'm looking at my results in detail and something's not quite right.
I'm running just for one fund (field Grants.FundNumber) and one fiscal year (FY2010)
Every few rows, it shows a VERY large number for FY2010 and I can't figure out why. I'm not sure if it's 'confused' & summing the data for all grant recipients?
September 21, 2011 at 9:32 am
Never mind, I think I got it. I removed 'sum' from the beginning of the case statement so it would give fiscal year totals for each recipient.
September 23, 2011 at 7:37 am
The solution presented works well only for the 2 specific fiscal years :
SUM (CASE WHEN Grants.PayDate > = '20100701' AND Grants.PayDate < '20110701' THEN Grants.PaymentAmount ELSE 0 END) AS FY2011,
SUM (CASE WHEN Grants.PayDate > = '20110701' AND Grants.PayDate < '20120701' THEN Grants.PaymentAmount ELSE 0 END) AS FY2012
A more generic solution to handle fiscal years is :
(1) extract the year and month (YYYY, MM) from the column PayDate
(2) add 6 to MM (because your fiscal year ends in month 6)
(3) if the above sum < 13, then the fiscal year-end is YYYY, else it is YYYY + 1.
So, for example :
if PayDate = 20110123, then (6 + 1) = 7, so fiscal year-end = 2011
if PayDate = 20110923, then (6 + 9) = 15, so fiscal year-end = 2012
As an extension of the above argument, if you need to deal with fiscal periods (ie, fiscal year and fiscal month), then :
(4) if the above sum < 13, then the fiscal month is the sum, else it is the sum - 12.
Thus, the 2 examples listed above would be :
if PayDate = 20110123, then (6 + 1) = 7, so fiscal period = 201107
if PayDate = 20110923, then (6 + 9) = 15, so fiscal period = 201203 (because 15 - 12 = 3).
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply