Dealing with a SQL codesmell...

  • Hi there,

    This was something that I wrote quick and dirty to solve a problem and upon further review it looks pretty shlocky. Basically I'm getting a calculated 'Amount' and I want to divide that among the next 3 months.

    Is there a cleaner way than this?

    SET @intMonthIncrement = 1

    WHILE @intMonthIncrement<=3
    BEGIN
    INSERT TableTest
    (
    ID
    ,AsOfDate
    ,Amount
    )
    SELECT ID= p.ID
    ,AsOfDate= dbo.fnsysMonthEnd(DATEADD(mm,@intMonthIncrement,@datAsOfDate))
    ,Amount= CASE WHEN s.BMV<>0

    THEN ((p.BMV/s.BMV)*TQF)/3

    ELSE TQF/3

    END

    FROM@tblPort p

    INNER JOIN @tblSum s

    ON p.BSGID= s.BSGID

    WHERE p.Include= 1

    SELECT @intMonthIncrement=@intMonthIncrement+1

    END

  • This should do it:

    INSERT TableTest(

    ID

    ,AsOfDate

    ,Amount

    )

    SELECT ID = p.ID

    , AsOfDate = dbo.fnsysMonthEnd(DATEADD(mm,mi.MonthIncr,@datAsOfDate))

    , Amount = CASE WHEN s.BMV0 THEN ((p.BMV/s.BMV)*TQF)/3

    ELSE TQF/3 END

    FROM @tblPort p

    INNER JOIN @tblSum s ON p.BSGID= s.BSGID

    CROSS Join (Select 1 as MonthIncr

    UNION ALL Select 2

    UNION ALL Select 3) mi

    WHERE p.Include= 1

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • definitely simpler than mine!

    thanks!

    My brain shuts off at the most inconvenient times 🙁

  • Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply