将日期范围拆分为几个月 [英] 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?

推荐答案

以下查询应该可以解决问题.CTE(WITH 子句)动态生成一些我们可以用来连接的 Month 数据.

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

这篇关于将日期范围拆分为几个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！