April 16, 2009 at 3:39 pm
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
April 16, 2009 at 3:54 pm
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]
April 16, 2009 at 8:20 pm
definitely simpler than mine!
thanks!
My brain shuts off at the most inconvenient times 🙁
April 16, 2009 at 9:00 pm
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