只是另一个选择使用 CROSS APPLY 还有一个特别的理货台
例子

Select A.[SourceSink]
      ,A.[Class]
      ,A.[ShadowPrice]
      ,A.[Round]
      ,A.[Period]
      ,B.[StartDate]
      ,B.[EndDate]
 From YourTable A
 Cross Apply (
                Select StartDate=min(D)
                      ,EndDate  =max(D)
                  From (
                         Select Top (DateDiff(DAY,[StartDate],[EndDate])+1) 
                                D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[StartDate]) 
                          From  master..spt_values n1,master..spt_values n2
                       ) B1
                  Group By Year(D),Month(D)
             ) B

退货

一个选项是递归查询。假设周期总是从一个月的第一天开始，到一个月的最后一天结束，如示例数据所示，这将是：

with cte as (
    select t.*, startDate newStartDate, eomonth(startDate) newEndDate
    from mytable t
    union all
    select 
        sourceSink,
        class,
        shadowPrice,
        period,
        startDate,
        endDate,
        dateadd(month, 1, newStartDate),
        eomonth(dateadd(month, 1, newStartDate))
    from cte
    where newStartDate < endDate
)
select * from cte

如果周期开始和结束于不同的月日，那么我们需要更多的逻辑：

with cte as (
    select 
        t.*, 
        startDate newStartDate, 
        case when eomonth(startDate) <= endDate then eomonth(startDate) else endDate end newEndDate
    from mytable t
    union all
    select 
        sourceSink,
        class,
        shadowPrice,
        period,
        startDate,
        endDate,
        dateadd(month, 1, datefromparts(year(newStartDate), month(newStartDate), 1)),
        case when eomonth(dateadd(month, 1, datefromparts(year(newStartDate), month(newStartDate), 1))) <= endDate
            then eomonth(dateadd(month, 1, datefromparts(year(newStartDate), month(newStartDate), 1)))
            else endDate
        end
    from cte
    where datefromparts(year(newStartDate), month(newStartDate), 1) < endDate
)
select * from cte