使用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