Create Monthly Duplicated Records using Date_Start and Date End in a New Table

Other SQL Server Topics
1

n 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 in a new table 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
2 
;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT
    Well, Operator, DATEADD(MONTH, (Months - 1), Date_Start) AS Date_Start, DateEnd, Months
FROM dbo.your_table_name ytn
INNER JOIN cte_tally100 t ON t.number BETWEEN 1 AND ytn.Months
1 Like
3

hi

here is the same kind of solution Scott gave ..
i was working on mine .. when Scott put up his

if for any reason mine helps !!! great

image
image1117×764 23 KB

declare @Starting_Data table (Well varchar(200), Operator varchar(200) , Date_Start Date , DateEnd Date , Months Int ) 

insert into @Starting_Data Values 
('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 )

;WITH Tally (N) AS
(
    SELECT top 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))   FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
select 'My SQL Output', 
  * 
from 
  Tally b, @Starting_Data a 
where 
  b.N<= a.Months 
order by 
   Well
1 Like