In SQL Server Management Studio, is there a way to take some excel data that has a Starting Date and Ending Date and Have it take the initial start date and Duplicate the record by month until the end date see examples I have a spreadsheet that has the data and I need to convert it to the one below by Month the number of records the well exist. So for example if the first well starts Jan 2018 and Ends Apr 2018 I need it to duplicate the row for Jan 2018, Feb 2018 and March 2018 it can even list April 2018 but stop duplicating the row leave in the data but don’t duplicate that record past its end date. Hope fully this makes sense. I can do it manually but trying to write a stored procedure that creates a new table from the Original table like the example below.
Starting Data
Well Operator Date_Start DateEnd Months
--------------------------------------------------------------------------------------
JIM TOM LONTOS 30 23S 28E RB MATADOR RESOURCES 1/1/2018 4/2/2018 3
ODIE 1606 BCE-MACH III LLC 1/1/2018 4/16/2018 3
SIEGRIST 1307 MARATHON OIL 1/1/2018 5/23/2018 4
SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018 11
Ending Data
Date Lease Operator Start_date End_Date
--------------------------------------------------------------------------------------
Jan-18 JIM TOM LONTOS 30 23S 28E RB MATADOR RESOURCES 1/1/2018 4/2/2018
Feb-18 JIM TOM LONTOS 30 23S 28E RB MATADOR RESOURCES 1/1/2018 4/2/2018
Mar-18 JIM TOM LONTOS 30 23S 28E RB MATADOR RESOURCES 1/1/2018 4/2/2018
Jan-18 ODIE 1606 BCE-MACH III LLC 1/1/2018 4/16/2018
Feb-18 ODIE 1606 BCE-MACH III LLC 1/1/2018 4/16/2018
Mar-18 ODIE 1606 BCE-MACH III LLC 1/1/2018 4/16/2018
Jan-18 SIEGRIST 1307 MARATHON OIL 1/1/2018 5/23/2018
Feb-18 SIEGRIST 1307 MARATHON OIL 1/1/2018 5/23/2018
Mar-18 SIEGRIST 1307 MARATHON OIL 1/1/2018 5/23/2018
Apr-18 SIEGRIST 1307 MARATHON OIL 1/1/2018 5/23/2018
Jan-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
Feb-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
Mar-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
Apr-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
May-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
Jun-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
Jul-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
Aug-18 SILVERTIP 76-7 UNIT A OCCIDENTAL PETROLEUM 1/1/2018 12/6/2018
One option uses a recursive query:
with cte as (
select datefromparts(year(date_start), month(date_start), 1) dt, well, operator, date_start date_end
from mytable t
union all
select dateadd(month, 1, dt), well, operator, date_start, date_end
from cte c
where c.dt < end_date
)
select * from cte