For each day between two dates, add a row with the same information, 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 SQL query can I find to find out how to make this table:

 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 done only once and on a very small data set. Optimization is not required.

Answer 1

You may 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 

EXIT:

 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 

Answer 2

You can do this with 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 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] 

Demonstration of both queries: SQL Fiddle

Answer 3

 ;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 

Answer 4

The problem that I encountered in this question / answer is that it is only for one entry. In this SQL answer post, I found a simple and effective solution on how to convert a string with a date range to many rows with each date .

RichardTheKiwi's solution involves adding new date records based on an integer table (list) and joining the original table by evaluating the date range using the datediff function. You can get a list of integers directly from the SQL Server main database ( SELECT master..spt_values WHERE v.type='P' ). this is

The google search term is sql spt_values and there are many interesting blog posts about this table. For instance...

• Generating a set or sequence without loops - part 2
• Tricks using the spt_values ​​system table - SQL Server

Complete 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 | +------------+------------+-----------+---------------+-------------+