3

I have a table with following columns:

ID startdate enddate

I want the rows of this table to be repeated as many times as the difference between startdate and enddate along with a column which gives all the dates between these two days for each id in the table. So, my new table should be like this:

ID Date

A startdate
A startdate +1 day
A startdate +2 days (till enddate)
B startdate
B startdate + 1 day ....

Please note that I have different start and end dates for different IDs.

I tried the answer for the following question, but this doesn't work:

Mysql select multiple rows based on one row related date range

Community
  • 1
  • 1
Sun Mun
  • 45
  • 1
  • 7

3 Answers3

4

Here's one approach.

This uses an inline view (aliased as i to generate integer values from 0 to 999, and that is joined to your table to generate up to 1000 date values, starting from startdate up to enddate for each row.

The inline view i can be easily extended to generate 10,000 or 100,000 rows, following the same pattern.

This assumes that the startdate and enddate columns are datatype DATE. (or DATETIME or TIMESTAMP or a datatype that can be implicitly converted to valid DATE values.

SELECT t.id
     , t.startdate + INTERVAL i.i DAY AS `Date`
  FROM ( SELECT d3.n*100 + d2.n*10 + d1.n AS i
           FROM ( SELECT 0 AS n 
                   UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                ) d1
          CROSS
           JOIN ( SELECT 0 AS n 
                   UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                ) d2
          CROSS
           JOIN ( SELECT 0 AS n 
                   UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                ) d3
       ) i
  JOIN mytable t
    ON i.i <= DATEDIFF(t.enddate,t.startdate)
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thank you for your reply @spencer7593 but I tried Robin's code first and that is working fine. Thank you for your help. – Sun Mun Feb 09 '13 at 00:31
  • 1
    @Sun Mun: with Robin's approach, your query is dependent on the existence of a table, and the contents of that table. For some developers in some large shops, with DBAs that will create new tables seemingly only after an Act of Congress and an Environmental Impact Study from the Army Corps of Engineers... generating a set of integers on the fly is a convenient way to go. – spencer7593 Feb 09 '13 at 00:37
  • Thank you so much. I have been searching for ages for this answer and yours seems to actually work with what I am trying to do :) – Jera May 23 '13 at 11:02
  • @user1942165 : This is a technique that has worked for me, and it does not require creating and populating a table, or creating a user defined function; this uses just plain old SQL. (Some other databases have more convenient ways of generating a sequence of integers. The approach demonstrated in this answer uses "base 10", but this same approach could just as easily use base 2, or any other base. We're just more familiar with base 10.) – spencer7593 May 24 '13 at 05:41
  • A quick one on the above.... 9 is repeated in each part, needs to be 8, other wise it gives odd results. – Jon Selby Feb 20 '19 at 10:26
  • @JonSelby... yah, absolutely. The intent was to generate unique, consecutive values... I will edit this six year old answer to correct it. – spencer7593 Feb 20 '19 at 14:55
2

You need a numbers table... create a temporary table or dummy table that contains the numbers 1 to X (X being the maximum possible difference between the two dates)

Then join to that table using a date diff

I'm afraid I'm SQL Server and so not sure if the datediff functions work the same way in mysql, but you should get the idea.

SELECT
    DateTable.Id,
    DATEADD(dd, NumbersTable.Number, DateTable.StartDate)
FROM
    DateTable
INNER JOIN
    NumbersTable
ON
    DATEADD(dd, NumbersTable.Number, DateTable.StartDate) <= DateTable.EndDate
ORDER BY
    DateTable.Id,
    DATEADD(dd, NumbersTable.Number, DateTable.StartDate)
Robin Day
  • 100,552
  • 23
  • 116
  • 167
  • Works perfectly. Thank you so much Robin Day. If anyone looks at this post in future, you could use the code given by @Pittsburgh DBA on http://stackoverflow.com/questions/186756/how-to-generate-a-range-of-numbers-in-mysql to generate a table with 0-999 numbers. – Sun Mun Feb 09 '13 at 00:19
  • Also a note for anyone that looks at this post in the future, the syntax in this answer is not valid for MySQL. (MySQL has a `DATE_ADD` function and an `ADDDATE` function, and both have different arguments than the (SQL Server) DATEADD function given in this answer. MySQL also supports a convenient "date + INTERVAL n unit" syntax. Also note that a "numbers table" is a convenience, but it's not actually "needed". A set of integers can be generated on the fly, without the "need" of a numbers table. – spencer7593 Feb 09 '13 at 00:42
2

I know its very late to answer but still one more answer using recursive cte

with  recursive cte ( id, startdate) as
(
select id,startdate  from test t1
union all
select t2.id,(c.startdate + interval '1 day')::date
from test t2
 join cte c on c.id=t2.id and (c.startdate + interval '1 day')::date<=t2.enddate
)
select id,startdate as date from cte
order by id, startdate

its PostgreSQL specific, but it should work in other relational databases with little bit change in Date function.

YogeshR
  • 1,606
  • 2
  • 22
  • 43