As WITH is not allowed in this case, I did some replacement:
FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))
FYPrevStart = DateAdd(yy,-1,FYStart)
FYPrevStart = DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))
FYNextStart = DateAdd(yy,1,FYStart)
FYNextStart = DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))
The final working code is:
SELECTITSDivisionID
,A = Sum(Case When d.DocStatus = 'Retired' and d.RetiredDate >= DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) and d.RetiredDate < DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)) 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 >= DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)) and d.TargetClosureDate < DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) Then 1 Else 0 End)
,D = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) 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
WHERE DocType = 1
AND DocStatus IN ('Active','Retired')
GROUP BY ITSDivisionID
It makes the whole query [highlight=#ffff11]much more difficult to read and maintain[/highlight], but good thing is it will accepted by the rule to manually create a Virtual Table in Dundas Dashboard.
I have not figured out how to do the math.