Pesonally, I think that option 3 is the worst (it sounds like you're setting up to write embedded code, which is a huge mistake for maintenance, IMHO). Option 2 would be the best but it's your problem. Here's how you can do option 3 and still achieve what I was talking about.
WITH
cteFYStart AS (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))
,cteFYAll AS (SELECT FYStart
,FYNextStart = DateAdd(yy,1,FYStart)
,FYPrevStart = DateAdd(yy,-1,FYStart)
FROM cteFYStart)
SELECTITSDivisionID
,A = Sum(Case When d.DocStatus = 'Retired' and d.RetiredDate >= fy.FYPrevStart and d.RetiredDate < fy.FYStart Then 1 Else 0 End)
,B = Sum(Case When d.DocStatus = 'Active' Then 1 Else 0 End)
,C = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= fy.FYStart and d.TargetClosureDate < fy.FYNextStart Then 1 Else 0 End)
,D = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= fy.FYNextStart Then 1 Else 0 End)
,Critical = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 4 Then 1 Else 0 End)
,High = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 3 Then 1 Else 0 End)
,Medium = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 2 Then 1 Else 0 End)
,Low = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 1 Then 1 Else 0 End)
,TBD = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 0 Then 1 Else 0 End)
FROM dbo.Document d
CROSS JOIN cteFYAll fy
WHERE DocType = 1
AND DocStatus IN ('Active','Retired')
GROUP BY ITSDivisionID
;
Again, the code isn't yet complete. I'm waiting for you to think outside the box a bit and make it so BCD and DRR are calculated without repeating code and to add the "Division" name column using just one more CTE.
--Jeff Moden
Change is inevitable... Change for the better is not.