SQL在日期范围内的分割数 [英] SQL split number over date range

问题描述

我有一个表，例如该数据

I have a table with, for example this data

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

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

Iwant to have a query that split the dates so I have the amount splitted out over the year like this :

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年有一个月，而2014年的75岁是因为这有3个月

The 25 in 2013 is because 2013 has one month and 75 in 2014 because this has 3 months

可以在T-SQL中做到这一点？

Is there a way to do this in T-SQL?

预先感谢！

推荐答案

使用 spt_values 表创建日历表，然后加入

Use spt_values table to create a calendar table, then join to your table to split date range into any part you want.

如果按年划分并按月划分金额，则可以：

If split by year and divide amount by months you could:

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 FIDE DEMO 。

这篇关于SQL在日期范围内的分割数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！