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
 B   xyz1    2011-03-01  2011-03-31

Can somebody help me with this?

回答1:

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

回答2:

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