Add a row with the same info but only that day in the start/end columns

Question

I have a table, with types integer, datetime, datetime

ID	STARTDATE	ENDDATE
600097	2023-01-30	2023-02-02
602025	2023-09-02	2023-09-04

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

ID	STARTDATE	ENDDATE
600097	2023-01-30	2023-01-30
600097	2023-01-31	2023-01-31
600097	2023-02-01	2023-02-01
600097	2023-02-02	2023-02-02
602025	2023-09-02	2023-09-02
602025	2023-09-03	2023-09-03
602025	2023-09-04	2023-09-04

I've tried using the following query, but I'm having trouble when there are two rows with different IDs

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

What have you already tried? Please have a read of the [ask] section of the [help] — freefaller, Jul 20 '23 at 07:36

Zhorov · Answer 1 · 2023-08-08T12:14:45.950

For a ... ran once ... calculation you have at least these options:

A numbers table, generated using a JSON-based approach and an APPLY operator. Starting from SQL Server 2016 you may generate a serie of numbers using OPENJSON(). The idea is to build a JSON array with the correct number of items ([1, 1, 1, ..., 1]) and parse this array with OPENJSON() and default schema. The result is a table with columns key, value, type and the key column returns the 0-based index of each item in the array.
A numbers table, generated with GENERATE_SERIES() and an APPLY operator. Starting from SQL Server 2022 you may use GENERATE_SERIES() to build the necessary serie of numbers.
A recursive query.

Sample data:

SELECT *
INTO Data
FROM (VALUES
   (600097, CONVERT(date, '20230130'), CONVERT(date, '20230202')),
   (602025, CONVERT(date, '20230902'), CONVERT(date, '20230904'))
) v (ID, STARTDATE, ENDDATE)

Statement using OPENJSON():

SELECT 
   d.ID, 
   DATEADD(day, CONVERT(int, j.[key]), d.STARTDATE) AS STARTDATE,
   DATEADD(day, CONVERT(int, j.[key]), d.STARTDATE) AS ENDDATE
FROM Data d
CROSS APPLY OPENJSON('[1' + REPLICATE(',1', DATEDIFF(day, d.STARTDATE, d.ENDDATE)) + ']') j

Statement using GENERATE_SERIES():

SELECT 
   d.ID, 
   DATEADD(day, s.[value], d.STARTDATE) AS STARTDATE,
   DATEADD(day, s.[value], d.STARTDATE) AS ENDDATE
FROM Data d
CROSS APPLY GENERATE_SERIES(0, DATEDIFF(day, d.STARTDATE, d.ENDDATE)) s

Recursive query:

; WITH rCTE AS (
   SELECT d.ID, d.STARTDATE, d.STARTDATE AS ENDDATE
   FROM Data d
   UNION ALL
   SELECT r.ID, DATEADD(day, 1, r.STARTDATE), DATEADD(day, 1, r.STARTDATE)
   FROM Data d
   INNER JOIN rCTE r ON (r.ID = d.ID) AND (r.ENDDATE < d.ENDDATE)
)
SELECT * 
FROM rCTE
ORDER BY ID, STARTDATE

Result:

ID	STARTDATE	ENDDATE
600097	2023-01-30	2023-01-30
600097	2023-01-31	2023-01-31
600097	2023-02-01	2023-02-01
600097	2023-02-02	2023-02-02
602025	2023-09-02	2023-09-02
602025	2023-09-03	2023-09-03
602025	2023-09-04	2023-09-04

Add a row with the same info but only that day in the start/end columns

1 Answers1