Jeff Moden (4/12/2014)

---

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.