SQL在日期范围内的分割数

Question

我有一个表，例如此数据

ID |start_date  |end_date   |amount
---|------------|-----------|-------
a1 |2013-12-01  |2014-03-31 |100

我想有一个查询，将日期分开，所以我有像这样一年中分开的金额：

ID |org_start_date  |org_end_date   |new_start_date  |new_end_date    |amount
---|----------------|---------------|----------------|----------------|-------
a1 |2013-12-01      |2014-03-31     |2013-12-01      |2013-12-31      |25
a1 |2013-12-01      |2014-03-31     |2014-01-01      |2014-03-31      |75

2013年有25个月是因为2013年有1个月，而2014年有75个月是因为有3个月

有没有办法在T-SQL中做到这一点？

提前谢谢！

Answer 1

使用spt_values表格创建日历表格，然后将其加入表格以将日期范围划分为所需的任何部分。

如果按年份除以金额，然后除以月份，您可以：

with dates as
(
select number,DATEADD(day,number,'20130101') as dt
    from master..spt_values
    where number between 0 and 1000 AND TYPE='P'
)
select
    m.start_date as org_start_date,
    m.end_date as org_end_date,
    min(d.dt) as new_start_date,
    max(d.dt) as new_end_date,
    m.amount*count(distinct month(d.dt))/(datediff(month,m.start_date,m.end_date)+1) as amount
from 
    MonthSplit m
join
    dates d
on 
    d.dt between m.start_date and m.end_date
group by 
    m.start_date, m.end_date, year(d.dt),m.amount

这是SQL FIDDLE DEMO。