/*
	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

	CREATE TABLE #Calendar --drop table #dates_to_add 
		(
			FullDate              DATETIME 
			,YearMonthDay          AS Convert(varchar(8),FullDate,112)
			,YearMonth             AS Convert(varchar(6),FullDate,112)
			,RetentionYear         INT
			,DayWeekNo             AS DatePart(dw,FullDate)
			,DayWeekCode           AS CAST(LEFT(DATENAME(weekday,FullDate),3) as Char(3))
			,DayWeekDesc           AS CAST(DATENAME(weekday,FullDate) as Varchar(10))
			,DayMonthNo            AS CAST(DAY(FullDate) as SMALLINT)
			,DayYearNo             AS Cast(DatePart(dy,FullDate) AS SMALLINT)
			,WeekNo                AS CAST(DatePart(wk,FullDate) AS TINYINT)
			,MonthNo               AS CAST(MONTH(FullDate) AS TINYINT)
			,MonthCode             AS CAST(DATENAME(Month,FullDate) AS CHAR(3))
			,MonthDesc             AS CAST(DATENAME(Month,FullDate) AS VARCHAR(10))
			,RetentionMonthNo      INT
			,QuarterNo             AS CAST(DATEPART(qq,FullDate) as TINYINT)
			,YearNo                AS CAST(Year(FullDate) AS INT)
			,IsMonthEnd            AS CAST(CASE
										   WHEN ( [FullDate] < '9999-11-30 00:00:00.000' ) -- avoid overflow with poor data in PHX
										   THEN 
											   CASE CONVERT(DATE, [FullDate]) 
												 WHEN CONVERT(DATE, DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,[FullDate])+1,0))) THEN 1
												 ELSE 0
											   END
										   ELSE 0
										END  AS BIT)
			,IsQuarterEnd          AS CAST(CASE CONVERT(DATE, [FullDate]) 
												WHEN CONVERT(DATE, DATEADD(qq,DATEDIFF(qq,-1, [FullDate]),-1)) THEN 1
												ELSE 0
											END AS BIT)
			,IsYearEnd             AS CAST(CASE CONVERT(DATE, [FullDate]) 
												WHEN DATEADD(yy,DATEDIFF(yy,-1,[FullDate]),-1) THEN 1
												ELSE 0
											END AS BIT)
			,IsWeekend             AS CAST(CASE WHEN DateName(DW,FullDate) IN ('Saturday','Sunday') THEN 1 ELSE 0 END AS BIT)
			,IsHoliday             BIT DEFAULT (0)
			,AggregationType       VARCHAR(20) DEFAULT ('Period')
		)
		;

Insert into #Calendar
Select FullDate
From atable

--Or 

Insert into #Calendar
Select DateAdd(d,N,@Base)
From Tally

CREATE TABLE #Contracts
(
	ContractNumber INT
	,ContractStartDate DATETIME
	,ContractEndDate DATETIME
	,ContractAmount MONEY
);

INSERT INTO #Contracts
VALUES (1234,'01-Jul-2012','30-Nov-2012',5000.00)
,(5678,'01-Jul-2012','30-jun-2013',12000.00)

;WITH StartMonth (sm, nm) AS (
        SELECT MIN(ContractStartDate)
            ,DATEDIFF(month, MIN(DATEADD(month, DATEDIFF(month, 0, ContractStartDate), 0))
                ,MAX(DATEADD(month, 1, DATEADD(month, DATEDIFF(month, 0, ContractEndDate), 0))-1))
        FROM #Contracts), 
    Tally (n) AS (
        SELECT 0 UNION ALL
        SELECT TOP (SELECT nm FROM StartMonth) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES ($),($),($),($),($),($),($),($),($),($)) a(n)
        CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) b (n)),
    MyContracts AS (
        SELECT ContractNumber
            ,ContractMonth
            ,ContractAmount=CASE WHEN ContractStartDate > ContractMonth OR 
                ContractEndDate < ContractMonth THEN 0
                ELSE ContractAmount/(DATEDIFF(month, ContractStartDate, ContractEndDate)+1) END
        FROM #Contracts a
        CROSS APPLY Tally b
        CROSS APPLY (SELECT ContractMonth=DATEADD(month, n, ContractStartDate)) c)
SELECT ContractNumber
    ,[Jul]=MAX(CASE WHEN MONTH(ContractMonth) = 7 THEN ContractAmount END) 
    ,[Aug]=MAX(CASE WHEN MONTH(ContractMonth) = 8 THEN ContractAmount END) 
    ,[Sep]=MAX(CASE WHEN MONTH(ContractMonth) = 9 THEN ContractAmount END) 
    ,[Oct]=MAX(CASE WHEN MONTH(ContractMonth) = 10 THEN ContractAmount END) 
    ,[Nov]=MAX(CASE WHEN MONTH(ContractMonth) = 11 THEN ContractAmount END) 
    ,[Dec]=MAX(CASE WHEN MONTH(ContractMonth) = 12 THEN ContractAmount END) 
    ,[Jan]=MAX(CASE WHEN MONTH(ContractMonth) = 1 THEN ContractAmount END) 
    ,[Feb]=MAX(CASE WHEN MONTH(ContractMonth) = 2 THEN ContractAmount END) 
    ,[Mar]=MAX(CASE WHEN MONTH(ContractMonth) = 3 THEN ContractAmount END) 
    ,[Apr]=MAX(CASE WHEN MONTH(ContractMonth) = 4 THEN ContractAmount END) 
    ,[May]=MAX(CASE WHEN MONTH(ContractMonth) = 5 THEN ContractAmount END) 
    ,[Jun]=MAX(CASE WHEN MONTH(ContractMonth) = 6 THEN ContractAmount END) 
FROM MyContracts
GROUP BY ContractNumber

DROP TABLE #Contracts