For each day between two dates, add a row with the same info but only that day in the start/end columns

Question

I have a table, with types varchar, datetime, datetime:

NAME | START | END
Bob  | 10/30 | 11/2

What's a SQL query can I look up to find out how to make that table be?:

NAME | START | END
Bob  | 10/30 | 10/30
Bob  | 10/31 | 10/31
Bob  | 11/01 | 11/01
Bob  | 11/02 | 11/02

This is only ran once, and on a very small dataset. Optimization isn't necessary.

Year can be anything, the idea is that START and END will be of type datetime. — Justin Warner, Dec 15 '14 at 17:25
Why are the `START` and `END` fields in your output identical? — Hart CO, Dec 15 '14 at 17:42
Other parts of the product rely on a start/end date, my feature assumes one entry per day, so no start/end is needed, but must have it to satisfy other requirements. If that makes sense? — Justin Warner, Dec 15 '14 at 17:46

Pரதீப் · Accepted Answer · 2014-12-15T17:45:25.240

May be you need a Recursive CTE.

CREATE TABLE #dates(NAME  VARCHAR(50),START DATETIME,[END] DATETIME)

INSERT INTO #dates
VALUES      ('Bob','2014-10-30','2014-11-02')

DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM   #dates);

WITH cte
     AS (SELECT NAME,
                START,
                [END]
         FROM   #dates
         UNION ALL
         SELECT NAME,
                Dateadd(day, 1, start),
                Dateadd(day, 1, start)
         FROM   cte
         WHERE  start < @maxdate)
SELECT *
FROM   cte

OUTPUT :

name    START       END
----    ----------  ----------
Bob     2014-10-30  2014-10-30
Bob     2014-10-31  2014-10-31
Bob     2014-11-01  2014-11-01
Bob     2014-11-02  2014-11-02

I have. (Name/ Start / End ) Bob 2014-10-30 00:00:00.000 2014-11-02 It should Be SELECT NAME, START, START AS END FROM cte — mr R, Aug 08 '18 at 08:32

Hart CO · Answer 2 · 2014-12-15T17:44:13.873

14

You can do this with a recursive cte:

;with cte AS (SELECT Name,Start,[End]
              FROM YourTable
              UNION  ALL
              SELECT Name
                    ,DATEADD(day,1,Start)
                    ,[End]
              FROM cte
              WHERE Start < [End])
SELECT Name, Start, Start AS [End]
FROM cte

However, I suggest creating a calendar table and joining to it:

SELECT a.Name,b.CalendarDate AS Start, b.CalendarDate AS [End]
FROM YourTable a
JOIN tlkp_Calendar b
  ON b.CalendarDate BETWEEN a.[Start] AND a.[End]

Demo of both queries: SQL Fiddle

edited Dec 15 '14 at 17:44

answered Dec 15 '14 at 17:32

Hart CO

32,944
5
44
59

Other parts of the product rely on a start/end date, my feature assumes one entry per day, so no start/end is needed, but must have it to satisfy other requirements. If that makes sense? – Justin Warner Dec 15 '14 at 17:45
@HartCO Thank you! Your calendar table idea is brilliant. I used a date dimension table I had already created for a Data Warehouse dimensional model. Wanted to point out that your 2nd suggestion was a pretty straightforward approach. Thx. – AstroBernal Jul 17 '19 at 16:39

SherlockSpreadsheets · Answer 3 · 2019-04-04T13:46:33.890

The problem I had with this Question/Answer is it is for one record only. I found a simple and effective solution in this answer post -- SQL how to convert row with date range to many rows with each date.

The solution by "RichardTheKiwi" involves adding new date records based upon an Integer Table (list) and join to Source Table by evaluating the date range with datediff function. You can pull an integer list directly from SQL Server master database (SELECT master..spt_values WHERE v.type='P'). This

Google the search term sql spt_values and there are many interesting blog posts about this table. For example...

Full Solution:

--NAME | START | END
--Bob  | 10/30 | 11/2

DECLARE @SampleData as table 
    (PersonName nvarchar(50), StartDate date, EndDate date)
INSERT INTO @SampleData
    (PersonName, StartDate, EndDate)
VALUES
    ('Bob', '2019-10-30', '2019-11-02')
    , ('Joe', '2019-10-30', '2019-11-05')
;

WITH 
cteSampleData_RecordAdded AS
-- NOTE: Range record converted to daily records for 'SampleData'
(
    SELECT 
        T1.PersonName
        , T1.StartDate
        , T1.EndDate
        , DATEADD(d,v.number,T1.StartDate) AS [NewRecordDate]
        , DATEDIFF(day, T1.StartDate, T1.EndDate)+1 AS [QDaysActive]
    FROM 
        @SampleData T1
        -- Adds a record for each date in the range
        JOIN MASTER..spt_values v 
            ON v.type='P'AND v.number BETWEEN 0 AND datediff(d, T1.StartDate, T1.EndDate)
)

select * from cteSampleData_RecordAdded

Results:

+------------+------------+-----------+---------------+-------------+
| PersonName | StartDate  | EndDate   | NewRecordDate | QDaysActive |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 10/30/2019    | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 10/31/2019    | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 11/1/2019     | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 11/2/2019     | 4           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 10/30/2019    | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 10/31/2019    | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/1/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/2/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/3/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/4/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/5/2019     | 7           |
+------------+------------+-----------+---------------+-------------+

what if date range exceeds 2047 days? – Haider Ali Wajihi Nov 25 '20 at 12:17 — Haider Ali Wajihi, Nov 25 '20 at 12:17

score 2 · Answer 4 · answered Dec 15 '14 at 17:37

2

;WITH dates
 AS (SELECT (SELECT MIN(start) from table) as date,
     UNION ALL
     SELECT
            Dateadd(day, 1, date),
     FROM   dates
     WHERE  date < (SELECT MAX(end) from table))

SELECT name, date as start, date as end
from table
RIGHT JOIN dates on date between start and end

answered Dec 15 '14 at 17:37

overflowed

1,663
9
13

This will not work you cannot use `aggregate` on `recursive part` of `CTE` – Pரதீப் Dec 15 '14 at 17:40
That are subqueries, no aggregates on the cte. – overflowed Dec 15 '14 at 17:43
in sub-query you have used Aggregate which is not allowed in `recursive part of CTE` – Pரதீப் Dec 15 '14 at 17:45

For each day between two dates, add a row with the same info but only that day in the start/end columns

4 Answers4

Linked