Split multiple data range to multiple rows

Question

here is my problem:

I have a table:

FIELD_1   FIELD_2   FIELD_N   StartDate     EndDate       OTHER_FIELDS
value1    value2    valuen    2016-01-12    2016-05-12    othervalues  
value3    value4    valuen    2015-01-12    2015-05-12    othervalues  

I need to split the data range of multiple rows in other multiple rows.

As below :

StartDate                 EndDate                   other_columns
2016-01-12                2016-05-12                myvalues
2016-01-13                2016-05-12                myvalues
2016-01-14                2016-05-12                myvalues
..                        ..                        ..
..                        ..                        .. 
2015-01-12                2015-05-12                myvalues
2015-01-13                2015-05-12                myvalues
..                        ..                        ..

Here is my Code :

CREATE TABLE [dbo].[OUTPUT_TABLE](
    [STARTDATE] [datetime] NULL,
    [ENDDATE] [datetime] NULL,
    [OTHER_FIELDS] [nvarchar](30) NULL,
) ON [PRIMARY]

GO

DECLARE @cnt INT 
DECLARE @startDate DATETIME 
DECLARE @endDate DATETIME 
DECLARE @incr INT 
DECLARE @tempDate DATETIME 

SET @startDate=(SELECT [StartDate] 
                FROM   [dbo].[INPUT_TABLE]) 
SET @endDate=(SELECT [EndDate] 
               FROM   [dbo].[INPUT_TABLE]) 
SET @cnt=Datediff(dd, @startDate, @endDate) 
SET @incr=0 
SET @tempDate=Dateadd(dd, @incr, Cast(@startDate AS DATETIME)) 

WHILE @cnt >= 0   
  BEGIN 
      IF @cnt = 0 
        BEGIN 
            INSERT INTO  [dbo].[OUTPUT_TABLE]  
            VALUES     (@tempDate, 
                        @endDate, 
                        NULL 
                         ); 
        END 
      ELSE 
        BEGIN 
            INSERT INTO [dbo].[OUTPUT_TABLE]  
            VALUES     (@tempDate, 
                        Dateadd(dd, Datediff(dd, 0, @tempDate) + 1, -1), 
                        NULL 
                         ); 
        END 


      SET @tempDate=Dateadd(dd, @incr + 1, Dateadd(dd, Datediff(dd, 0, 
                                                       @startDate) 
                                           , 0)) 
      SET @cnt=@cnt - 1 
      SET @incr=@incr + 1 
  END 

At the moment the code is working in case of having a single row on the input table, but considering i need to iterate it on multiple rows i'm currently not founding a solution. Anyone here has any idea who can help me fix the issue?

Thank You Very Much in advance, Kind Regards, Luigi

Answer 1

Here is how you can leverage a tally table to make short work of this. No need for looping at all. You can create a tally table on the fly with ctes if you want. In my system I have a view that is defined like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

You really should become intimately familiar with the tally table. It has been dubbed as the "Swiss Army knife of t-sql".

Now we need the tables and data to finalize setting up the problem.

create table #InputTable
(
    SomeID int identity primary key
    , StartDate date
    , EndDate date
    , OTHER_FIELDS varchar(20)
)

insert #InputTable
(
    StartDate
    , EndDate
    , OTHER_FIELDS
)
select '2016-01-12', '2016-05-12', 'othervalues' union all
select '2015-01-12', '2015-05-12', 'othervalues'

CREATE TABLE [dbo].[OUTPUT_TABLE](
    [STARTDATE] [datetime] NULL,
    [ENDDATE] [datetime] NULL,
    ThisDate date,
    [OTHER_FIELDS] [nvarchar](30) NULL,
) ON [PRIMARY]

Now that we have the entire problem setup we can actually start working on the solution. Leveraging the tally table makes this super simple. Nothing more than a single insert statement.

insert OUTPUT_TABLE
(
    STARTDATE
    , ENDDATE
    , ThisDate
    , OTHER_FIELDS
)
select it.StartDate
    , it.EndDate
    , DATEADD(day, t.N - 1, it.StartDate)
    , it.OTHER_FIELDS
from #InputTable it
join cteTally t on t.N <= DATEDIFF(day, it.StartDate, it.EndDate) + 1

Now we can check to see if this really worked the way we think it should.

select * 
from OUTPUT_TABLE

Look at that. 243 rows, one with each date between the values of each start and end date from the base table. No loops, no cursors. All that is left is a little cleanup to remove the evidence of our legwork.

drop table OUTPUT_TABLE
drop table #InputTable

Answer 2

Maybe create a calendar table containing dates and then just join to it.

SELECT *
FROM input_table it
JOIN calendar c on c.date >= it.startdate and c.date <= it.enddate