halifaxdal (4/14/2014)

---

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.