Welcome to SSC, DDL would be nice but this is not really a difficult problem so I've mocked up the data to suit, but for future refrence the DDL (table defs) and sample data would be nice.

You can solve this with a Calendar Table, once you have a Calendar table in place its simple to then run a Cross tab to get the values, in place.

One minor issue could be if a contract starts and/or ends mid month, as you would need to split the contract value on a pro-rata basis for the partial months.

eg Contract Start date : 21/07/2012, contract end date = 31/12/2012.

which means that you may actually need to go to week or day level and then aggreate this back up to do the maths.

This should get you part way there, the only thing missing is the Cross Tab/Pivot of the values.

/*

Set up

*/

Declare @Base as Date='31-Dec-2000';

Create Table #Data

(

ContractNumber int

,ContractStartDate Datetime

,ContractEndDate Datetime

,ContractAmount Money

);

Create Table #Calendar

(

CalendarDate datetime

,CalendarMonth tinyint

,CalendarDay tinyint

,CalendarWeek smallint

,YearMonth int

);

/*

Create In line Tally Table

*/

WITH

L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows

L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows

L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows

L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows

L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows

L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

/*

Populate The Calendar table with

*/

Insert into #Calendar

Select Convert(Datetime,DateAdd(d,n,@Base)) CalendarDate

,DatePart(Month, DateAdd(d,n,@Base)) CalendarMonth

,DatePArt(d, DateAdd(d,n,@Base)) CalendarDay

,DatePArt(wk, DateAdd(d,n,@Base)) CalendarWeek

,convert(varchar(6),DateAdd(d,n,@Base),112) YearMonth

From Nums n

Where n<10000

/*

populate the Contract Table

*/

Insert into #Contract

Values (1234,'01-Jul-2012','30-Nov-2012',5000.00)

,(5678,'01-Jul-2012','30-jun-2013',12000.00)

;With Cte_Months

AS

(

Select Distinct

ContractNumber

,YearMonth

,DateDiff(m, d.ContractStartDate, d.ContractEndDate)+1 Months

From #Data d

JOIN #Calendar cal on Cal.CalendarDate Between d.ContractStartDate and d.ContractEndDate

)

Select

d.ContractNumber

,YearMonth

,Months

,ContractAmount/Months

From

#Data d

JOIN Cte_Months M on M.ContractNumber=d.ContractNumber

the problem you will face with the Cross tab/Pivot out of the data is that the Number of columns is likley to be variable, unless you can hand on heart state that no contract will be greater than a number of months.