I have a table with values for a start and end date. If the start and end date is not the same, I want to duplicate the rows for the dates in between, so that each date has his own row with the same information.
Of course I have read many solutions, but could still not figure out how to deal with my problem. I do not want to make a calendar table to help solve this problem. Therefore, this question does not solve my problem.
Here is a minimal reproducable example:
CREATE TABLE
myTable (
[SchoolId] int,
[StartDate] date,
[EndDate] date,
[SomeBit] bit,
[BigId] bigint,
);
INSERT INTO
myTable (
[SchoolId],
[StartDate],
[EndDate],
[SomeBit],
[BigId]
)
VALUES
(1, '20150101', '20150104', 0, 437457324555),
(2, '20150101', '20150101', 1, 4573467234),
(3, '20150102', '20150102', 0, 45756654565),
(4, '20150102', '20150103', 1, 4564576754),
(5, '20150105', '20150106', 1, 54745753)
;
SELECT * FROM myTable;
In this example, I would want an outcome that has four lines with schoolId 1, Somebit 0 and BigId 437… (so copying the data except for the start and end date) with start dates 2015-01-01, 2015-01-02, 2015-01-03 and 2015-01-04. The value of the end date won’t be used.
As for rows 2 and 3 the start and end date are the same, nothing should be added.
For rows 4 and 5 one row each should be added with startdates 2015-01-03 and 2015-01-05 respectively.
| SchoolID | StartDate | SomeBit | BigID |
|---|---|---|---|
| 1 | 20150101 | 0 | 437457324555 |
| 1 | 20150102 | 0 | 437457324555 |
| 1 | 20150103 | 0 | 437457324555 |
| 1 | 20150104 | 0 | 437457324555 |
| 2 | 20150101 | 1 | 4573467234 |
| 2 | 20150102 | 0 | 45756654565 |
| 4 | 20150102 | 1 | 4564576754 |
| 4 | 20150103 | 1 | 4564576754 |
| 5 | 20150105 | 1 | 54745753 |
| 5 | 20150106 | 1 | 54745753 |
In the real table, I am dealing with 100.000s of rows. I work with T-SQL in SSMS.
>Solution :
A solution for ISO SQL is :
SELECT *, DATEADD(day, value, [StartDate])
FROM myTable AS T
CROSS APPLY GENERATE_SERIES(0, DATEDIFF(DAY, [StartDate], [EndDate]))
GENERATE_SERIES has been add to SQL ISO standard and can be found in PostGreSQL and MS SQL Server.
This example if for MS SQL Server.