按月分解日期范围 [英] breaking up a date range by month

问题描述

我有几行数据，开始日期和结束日期通常是从月初到月底.但是，有时有些行是- 几个月之久- 从月中开始，到同月或下个月结束- 从月初开始，但在下个月中旬结束.

I have rows of data with a begin date and end date that are usually from the start of the month until the end of that month. However at times there are rows that are either - several months long - start in the middle of a month and end either the same month or a future month - begin at start of a month, but end somewhere int he middle of a future month.

我需要按月打破这些日期范围，但不知道如何去做.

i need to break these date ranges by month, but do not know how to go about this.

感谢任何指导.

谢谢，

推荐答案

您可以为此任务使用数字表.

You could use a number table for this task.

在下面的示例查询中，一个名为 master..spt_values 的系统表被用作数字表的替代:

In the sample query below a system table called master..spt_values is used as a replacement for the number table:

SELECT
  CASE WHEN BeginDate > MonthStart THEN BeginDate ELSE MonthStart END AS BeginDate,
  CASE WHEN EndDate   < MonthEnd   THEN EndDate   ELSE MonthEnd   END AS EndDate,
  …  /* other columns as needed */
FROM (
  SELECT
    d.*,  /* or you could be more specific here */
    (
      DATEADD(month, DATEDIFF(month, 0, d.BeginDate) + v.number, 0)
    ) AS MonthStart,
    DATEADD(day, -1,
      DATEADD(month, DATEDIFF(month, 0, d.BeginDate) + v.number + 1, 0)
    ) AS MonthEnd
  FROM RowsOfData d
    INNER JOIN master..spt_values v ON v.type = 'P'
      AND v.number BETWEEN 0 AND DATEDIFF(month, d.BeginDate, d.EndDate)
) s

每一行都被分成一系列行，其中 BeginDate 是实际的 BeginDate 或月初，取决于哪个值更大，同样对于结束日期.为了说明，以下行

Every row is split into a series of rows where BeginDate is either the actual BeginDate or the beginning of the month, depending on what value is greater, and likewise for EndDate. To illustrate, the following rows

BeginDate   EndDate
----------  ----------
2010-03-05  2010-03-24
2010-04-16  2010-05-05
2010-06-29  2006-08-12
2010-10-10  2011-02-01

会这样拆分:

BeginDate   EndDate
----------  ----------
2010-03-05  2010-03-24
2010-04-16  2010-04-30
2010-05-01  2010-05-05
2010-06-29  2010-06-30
2010-07-01  2010-07-31
2010-08-01  2010-08-12
2010-10-10  2010-10-31
2010-11-01  2010-11-30
2010-12-01  2010-12-31
2011-01-01  2011-01-31
2011-02-01  2011-02-02

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