SQL - 如何根据现有数据集中的日期范围为每个月生成行? [英] SQL - how do I generate rows for each month based on date ranges in existing dataset?

问题描述

假设我有一个数据集:

rowID |  dateStart  |   dateEnd  | Year | Month
121   | 2013-10-03  | 2013-12-03 | NULL | NULL
143   | 2013-12-11  | 2014-03-11 | NULL | NULL
322   | 2014-01-02  | 2014-02-11 | NULL | NULL

我希望 sql 根据 dateStart 和 dateEnd 生成以下数据源.请注意年和月分组.

And I want sql to generate the following datasource based on the dateStart and the dateEnd. Note the year and month grouping.

rowID |  dateStart  |   dateEnd  | Year | Month
121   | 2013-10-03  | 2013-12-03 | 2013 |   10
121   | 2013-10-03  | 2013-12-03 | 2013 |   11
121   | 2013-10-03  | 2013-12-03 | 2013 |   12
143   | 2013-12-11  | 2014-03-11 | 2013 |   12
143   | 2013-12-11  | 2014-03-11 | 2014 |    1
143   | 2013-12-11  | 2014-03-11 | 2014 |    2
143   | 2013-12-11  | 2014-03-11 | 2014 |    3
322   | 2014-01-02  | 2014-02-11 | 2014 |    1
322   | 2014-01-02  | 2014-02-11 | 2014 |    2

我很难把头放在这个上面.有什么想法吗?

I'm having a hard time wrapping my head around this one. Any ideas?

推荐答案

我发现通过创建一个整数列表然后使用它来增加日期来解决这些问题最容易.下面是一个例子:

I find it easiest to approach these problems by creating a list of integers and then using that to increment the dates. Here is an example:

with nums as (
      select 0 as n
      union all
      select n + 1 as n
      from nums
      where n < 11
     )
select rowid, datestart, dateend,
       year(dateadd(month, n.n, datestart)) as yr,
       month(dateadd(month, n.n, datestart)) as mon
from table t join
     nums n
     on dateadd(month, n.n - 1, datestart) <= dateend;

这篇关于SQL - 如何根据现有数据集中的日期范围为每个月生成行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！