Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Adding row for every date between two dates in SQL

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading