split date range into months

I would like to split the date range into respective months.

For example - I've a view which has data in the following way:

user project startdate   enddate
-----------------------------------
 A   abc1    2011-01-01  2011-12-31
 A   abc2    2011-01-01  2011-05-01
 B   xyz1    2011-01-01  2011-03-01

I want to be able to display the above data in this way:

user project startdate   enddate     
 A   abc1    2011-01-01  2011-01-31
 A   abc1    2011-02-01  2011-02-28
 A   abc1    2011-03-01  2011-03-31
 ----------------------------------
 A   abc2    2011-01-01  2011-01-31
 A   abc2    2011-02-01  2011-02-28
 ----------------------------------
 B   xyz1    2011-01-01  2011-01-31
 B   xyz1    2011-02-01  2011-02-28开发者_如何学C
 B   xyz1    2011-03-01  2011-03-31

Can somebody help me with this?

The following query should do the trick. The CTE (the WITH clause) dynamically generates some Month data that we can use to join against.

declare @test table (
    userid char(1),
    project char(4),
    startdate datetime,
    enddate datetime)

insert into @test
select 'A', 'abc1', '1/1/2011', '12/31/2011'
union select 'A', 'abc2', '1/1/2011', '5/1/2011'
union select 'B', 'xyz1', '1/1/2011', '3/1/2011'

--select * from @test

;with MonthList as (
    select 
        DATEADD(month, M, '12/1/1899') as 'FirstDay',
        dateadd(day, -1, dateadd(month, M + 1, '12/1/1899')) as 'LastDay',
        DATEADD(month, M + 1, '12/1/1899') as 'FirstDayNextMonth'
    from (
        select top 3000 ROW_NUMBER() over (order by s.name) as 'M'
        from master..spt_values s) s
)

select
    t.userid, t.project, ml.FirstDay, ml.LastDay
from
    @test t
    inner join MonthList ml
        on  t.startdate < ml.FirstDayNextMonth
            and t.enddate >= ml.FirstDay

Here is a procedure that gives you what you want. But, if you take the time to learn CTE, it is a much more powerful approach (2005 and onward).

create procedure dbo.ExpandProjects
as
begin
    DECLARE @UserN CHAR(1) 
    DECLARE @Proj CHAR(4)
    DECLARE @Start  DATETIME
    DECLARE @End    DATETIME
    DECLARE @DtLoop DATETIME
    DECLARE @dtNExt DATETIME

    SET NOCOUNT ON

    CREATE TABLE #ProjList
    (
        userName char(1),
        project  char(4),
        startDate dateTime,
        EndDate DateTime
    )


    DECLARE db_cursor CURSOR FOR  
    SELECT *
    FROM Projects

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @UserN,@Proj,@Start,@end

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        SET @DtLoop = STR(MONTH(@Start))+'/01/'+STR(YEAR(@Start))
        WHILE @DtLoop <= @End
        BEGIN
            SET @dtNext = DATEADD(m,1,@dtLoop)

            INSERT INTO #ProjList 
                        VALUES(@UserN,@Proj,@DtLoop,DateAdd(d,-1,@dtNExt))
            SET @DtLoop = @dtNext


        END

        PRINT @DtLoop

        FETCH NEXT FROM db_cursor INTO @UserN,@Proj,@Start,@end
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor

    SELECT * FROM #ProjList ORDER BY 1,2



end