基于开始结束日期 SQL Server 拆分行 [英] Split row based on start end date SQL Server

问题描述

假设我在表中有这些值

Package | Start Date | End date 
---------------------------------
PKG123  | 01-02-2013 | 31-01-2016
PKG456  | 15-06-2010 | 14-06-2014

我想写一个选择语句，它应该给我这样的结果:

I want to write a select statement that should give me results like:

Package | Start Date | End date
-----------------------------------
PKG123  | 01-02-2013 | 31-01-2014
PKG123  | 01-02-2014 | 31-01-2015
PKG123  | 01-02-2015 | 31-01-2016
PKG456  | 15-06-2010 | 14-06-2011
PKG456  | 15-06-2011 | 14-06-2012
PKG456  | 15-06-2012 | 14-06-2013
PKG456  | 15-06-2013 | 14-06-2014

有没有办法在 TSQL 中做到这一点?

Is there a way to do it in TSQL?

请注意，我不是要查找两个日期之间的完整日期列表，而是要根据 12 个月的持续时间将一行拆分为多行.

Please note that I am not looking for a complete list of dates between two dates but looking for splitting of one row into multiple rows based on a duration of 12 months.

我使用的是 SQL Server 2008 R2.

I am using SQL Server 2008 R2.

推荐答案

这是使用递归 CTE 的方法:

This is how you can do it using a recursive CTE:

;with CTE as (
  select package, startDate, EndDate from data
  union all
  select package, dateadd(month, 12, startDate), EndDate
  from CTE where EndDate > dateadd(month, 12, startDate)
)

select 
  package,
  startdate,
  case when enddate <= dateadd(month, 12, startdate)
    then enddate else dateadd(day, -1, dateadd(month, 12, startdate)) end
    as enddate
From
  CTE 
order by package, startdate

CTE 将从表中取出行，如果开始日期 + 12 个月小于结束日期，则递归选择新行.CTE 之外的选择将确定要使用的行中的哪个值，开始日期 + 12 个月或结束日期.

The CTE will take the rows from the table, and then recursively select new row if the startdate + 12 month is less than the end date. The select outside the CTE will determine which value from the row to be used, startdate + 12 months or end date.

SQL 小提琴:http://sqlfiddle.com/#!6/5bfbf/4

这篇关于基于开始结束日期 SQL Server 拆分行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！