Home Forums SQL Server 2008 T-SQL (SS2K8) Convert the stored procedure to "Standard SQL Select" RE: Convert the stored procedure to "Standard SQL Select"

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)