Let's say I have these values in a table
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
Is there a way to do it in TSQL?
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.
I am using SQL Server 2008 R2.
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
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 Fiddle: http://sqlfiddle.com/#!6/5bfbf/4
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With