Loop through rows and exploding date range

Question

I have a task table where I want to create a view on that can do the following: 1. Iterate through each row 2. Explode each day between start and end date into a new row 3. Insert the average task work in a new column

This is the table:

CREATE TABLE #InputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int 
)

INSERT INTO #InputTABLE VALUES('2298aas','2018-06-06','2018-06-12',200);

I have gotten so far as to get 2 and 3 solved, but I really struggle with the iteration part. The code below will fail if more that one row is present in the InputTABLE:

CREATE TABLE #OutputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int 
)

DECLARE @taskid varchar (200)
DECLARE @cnt int
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @incr int
DECLARE @tempDate datetime 
DECLARE @work int
DECLARE @averagework int

SET @taskid=(Select TaskID from #InputTABLE)
SET @startDate=(Select startdate from #InputTABLE)
SET @endDate=(Select enddate from #InputTABLE)
SET @cnt=DATEDIFF(dy,@startDate,@endDate)
SET @incr=0

SET @tempDate=DATEADD(dy,@incr,Cast(@startDate As datetime))
SET @work=(Select TaskWork from #InputTABLE)
SET @averagework= @work/@cnt

WHILE @cnt>=0
BEGIN

   IF @cnt = 0 
      BEGIN
         INSERT INTO #OutputTABLE VALUES(@taskid,@tempDate,@endDate,@averagework);
      END
   ELSE
      BEGIN
         insert into #OutputTABLE values(@taskid,@tempDate,DATEADD(dy, DATEDIFF(dy,0,@tempDate)+1, -1),@averagework);
      END
   SET @tempDate=DATEADD(dy,@incr+1,DATEADD(dy,DATEDIFF(dy,0,@startDate),0))

   SET @cnt=@cnt-1
   SET @incr=@incr+1

   END

I thought about implementing the solution using a cursor from this, but I'm unsure on how to do it? I am also worried by the comments about looping through rows is bad for performance, so any advice on that is much appreciated!

The table is about 15.000 rows and the average date range is about 31 days, so the view will be circa 465.000 rows. Not a high number, but the table is continually growing so I might also need to limit the view to only the last two years.

Answer 1

Your question is not very clear, but my magic cystall ball tells me, that you might be looking for this:

SET DATEFORMAT ymd;
CREATE TABLE #InputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int 
);

INSERT INTO #InputTABLE VALUES('six days','2018-06-06','2018-06-12',200)
                             ,('one day','2018-06-06','2018-06-07',200);

SELECT TaskID
      ,DATEADD(DAY,B.Numbr,startdate) AS ExplodingDate
      ,CAST(TaskWork AS DECIMAL(10,4))/A.DayDiff
FROM #InputTABLE
CROSS APPLY(SELECT DATEDIFF(DAY,startdate,enddate) AS DayDiff) A
CROSS APPLY(SELECT TOP (A.DayDiff) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS Numbr FROM master..spt_values) B

DROP TABLE #InputTABLE;

the result

TaskID      ExplodingDate           (Kein Spaltenname)
six days    2018-06-06 00:00:00.000 33.333333333333333
six days    2018-06-07 00:00:00.000 33.333333333333333
six days    2018-06-08 00:00:00.000 33.333333333333333
six days    2018-06-09 00:00:00.000 33.333333333333333
six days    2018-06-10 00:00:00.000 33.333333333333333
six days    2018-06-11 00:00:00.000 33.333333333333333
one day     2018-06-06 00:00:00.000 200.000000000000000

Short explanation
The first APPLY computes the difference in days between your two dates.
The second APPLY uses a trick with TOP and ROW_NUMBER to create a tally table on the fly.
This will create as many rows per input row, as there are days between start and end date.
The rest is simple computation...

UPDATE A complete example with a persistant table

CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE dbo.RunningNumbers(Number INT NOT NULL
                               ,CalendarDate DATE NOT NULL
                               ,CalendarYear INT NOT NULL
                               ,CalendarMonth INT NOT NULL
                               ,CalendarDay INT NOT NULL
                               ,CalendarWeek INT NOT NULL
                               ,CalendarYearDay INT NOT NULL
                               ,CalendarWeekDay INT NOT NULL);

DECLARE @CountEntries INT = 100000;
DECLARE @StartNumber INT = 0;


WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1
    E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
    E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
    E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
    CteTally AS
    (
        SELECT TOP(ISNULL(@CountEntries,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartNumber,0) As Nmbr
        FROM E8
    )
INSERT INTO dbo.RunningNumbers
SELECT CteTally.Nmbr,CalendarDate.d,CalendarExt.*
FROM CteTally
CROSS APPLY
(
    SELECT DATEADD(DAY,CteTally.Nmbr,{ts'1900-01-01 00:00:00'})
) AS CalendarDate(d)
CROSS APPLY
(
    SELECT YEAR(CalendarDate.d) AS CalendarYear
          ,MONTH(CalendarDate.d) AS CalendarMonth
          ,DAY(CalendarDate.d) AS CalendarDay
          ,DATEPART(WEEK,CalendarDate.d) AS CalendarWeek
          ,DATEPART(DAYOFYEAR,CalendarDate.d) AS CalendarYearDay
          ,DATEPART(WEEKDAY,CalendarDate.d) AS CalendarWeekDay
) AS CalendarExt;
GO

SET DATEFORMAT ymd;
CREATE TABLE #InputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int 
);

INSERT INTO #InputTABLE VALUES('six days','2018-06-06','2018-06-12',200)
                             ,('one day','2018-06-06','2018-06-07',200);

SELECT TaskID
      ,B.CalendarDate
      ,CAST(TaskWork AS DECIMAL(10,4))/(A.DayDiff+1)
FROM #InputTABLE
CROSS APPLY(SELECT DATEDIFF(DAY,startdate,enddate) AS DayDiff) A
CROSS APPLY(SELECT * FROM dbo.RunningNumbers WHERE CalendarDate BETWEEN startdate AND enddate) B

DROP TABLE #InputTABLE;
GO

USE master;
GO

DROP DATABASE TestDB;