create a tally table and use it like below
;With Months
AS
(
SELECT id,Startdate,1 AS MonthVal,Maintermsperiod,annualamount,annualamount/Maintermsperiod AS MonthAmt
FROM Table
UNION ALL
SELECT id,DATEADD(mm,1,Startdate),MonthVal + 1,Maintermsperiod,annualamount,MonthAmt
FROM Months
WHERE MonthVal + 1 < = Maintermsperiod
)
SELECT id,YEAR(Startdate),annualamount AS Sales,MonthVal,MonthAmt
FROM Months
OPTION(MAXRECURSION 0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
How to repate a value for all months
Posted - 02/26/2013 : 23:46:15
