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.
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
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
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 |
+------------+------------+-----------+---------------+-------------+
;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
aggregate on recursive part of CTE –
Flyboat recursive part of CTE –
Flyboat The calendar solution mentioned in Hart CO's answer is always my go-to solution for this, but I'll add that you can create your own "calendar table" on the fly using recursive CTEs
This answer is slightly different to the other ones because it shows that you don't need to recurse over the initial table -- which makes this a more "copy-and-paste"-able solution
I would still recommend using a dedicated calendar table in your database in the vast majority of cases
In SQL Server, you don't have to include FROM in your queries, so you can spin up a "calendar table" pretty quickly between any dates that you want (almost, more details below):
WITH dates AS (
SELECT CAST('2014-01-01' AS DATE) AS date_axis
UNION ALL
SELECT DATEADD(DAY, 1, date_axis)
FROM dates
WHERE date_axis < '2015-01-01'
)
SELECT *
FROM dates
OPTION(MAXRECURSION 500)
;
This creates a table with a single column, date_axis, and a row for every day between 2014-01-01 and 2015-01-01, inclusive
I've hard-coded 2014-01-01 and 2015-01-01, but you could use variants of this to work with whatever you need -- such as passing in values at runtime (if the query is run via a BI tool or script), or by building relatively off of the current date (if you only need a relative range):
WITH dates AS (
SELECT DATEADD(DAY, -365, CAST(GETDATE() AS DATE)) AS date_axis
UNION ALL
SELECT DATEADD(DAY, 1, date_axis)
FROM dates
WHERE date_axis < CAST(GETDATE() AS DATE)
)
SELECT *
FROM dates
OPTION(MAXRECURSION 500)
;
The OPTION(MAXRECURSION 500) is SQL Server syntax for setting up how many iterations you can do in the recursive CTE. Since each loop corresponds to adding a day, using OPTION(MAXRECURSION 500) would error if you needed more than 500 days (the default is 100, by the way)
The max you can set this to is 32767 which is just under 90 years -- if you need more days than this, you should definitely consider creating a physical calendar table instead 😝
To answer this question, another option for setting the bounds for the recursive CTE are to use some variables (in databases that support them) or any other supported mechanism to pull minimum and maximum bounds:
DECLARE @MINDATE DATE = (SELECT MIN("start") FROM example);
DECLARE @MAXDATE DATE = (SELECT MAX("end") FROM example);
WITH dates AS (
SELECT @MINDATE AS date_axis
UNION ALL
SELECT DATEADD(DAY, 1, date_axis)
FROM dates
WHERE date_axis < @MAXDATE
)
SELECT *
FROM dates
OPTION(MAXRECURSION 500)
;
Note that Pரதீப் is right when they say that overflowed's answer won't work because of aggregates in the (sub-queries of the) recursive CTE -- at least, for SQL Server it isn't allowed, so the variables approach would be more correct for the OPs database
The last part is then just joining the original table back onto the "calendar table" to get the output:
DECLARE @MINDATE DATE = (SELECT MIN("start") FROM example);
DECLARE @MAXDATE DATE = (SELECT MAX("end") FROM example);
WITH dates AS (
SELECT @MINDATE AS date_axis
UNION ALL
SELECT DATEADD(DAY, 1, date_axis)
FROM dates
WHERE date_axis < @MAXDATE
)
SELECT
"name",
dates.date_axis AS "start",
dates.date_axis AS "end"
FROM dates
INNER JOIN example
ON dates.date_axis BETWEEN example."start" AND example."end"
OPTION(MAXRECURSION 32767)
;
This will now work for any number of rows in the original table and so I've whacked up the max recursion option to allow for a wide range of dates (but you might want to set a more sensible limit, if you go this route)
The full db<>fiddle for this is available at:
© 2022 - 2024 — McMap. All rights reserved.
STARTandENDfields in your output identical? – Bowfin