LuigiVe LuigiVe - 24 days ago 5
SQL Question

Split multiple data range to multiple rows

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

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