List all dates within some periods

Question

I have the following table of projects with their activity periods (periods are defined with FROM and TO dates):

ID | ProjID | ActiveFrom | ActiveTo
===+========+============+============
 1 |     20 | 2018-01-01 | 2018-01-04
 2 |     20 | 2018-02-05 | 2018-02-07
 3 |     20 | 2018-02-20 | 2018-02-22
 4 |     30 | 2018-01-15 | 2018-02-15

There is arbitrary number of activity periods for a project.

I need a query which will return all activity dates for a given project. For example for ProjID=20 the result should be:

you seem to have reposted your question: https://stackoverflow.com/questions/53403238/is-a-date-within-some-of-periods — Mitch Wheat, Nov 21 '18 at 00:41
I was watching the following question https://stackoverflow.com/questions/9295616 but expect that there would be easier solution — sbrbot, Nov 21 '18 at 00:45
Always when I see that my idea seems too complicated (for example looping through all periods and looping all dates in each period) I ask here more experienced experts and many times they offer me (and others) more clever and better solution. — sbrbot, Nov 21 '18 at 00:49
SO is not a code writing service. We're glad to help once you've made an effort to solve the problem yourself first and run into difficulties. Once you've done so, you can explain the problem you've encountered, include the relevant code from your efforts to solve it, and ask a specific question, and we'll try to assist. You've included absolutely no attempt or shown any effort to do anything yourself in this question. — Ken White, Nov 21 '18 at 01:03
I think it is duplicate. Check this: https://stackoverflow.com/questions/2157282/generate-days-from-date-range/45951982#45951982 — fifonik, Nov 21 '18 at 03:05

score 0 · Accepted Answer · answered Nov 21 '18 at 01:30

Do you mean like this :

SET @row_number = 0;
SELECT DATE_ADD(DATE_ADD(ActiveFrom, INTERVAL -1 DAY), INTERVAL x.D DAY) AS ActivityPeriods
FROM test
LEFT JOIN(SELECT *, (@row_number:=@row_number + 1) AS D  FROM information_schema.COLUMNS) AS x ON x.D <= DATEDIFF(ActiveTo,ActiveFrom) +1
WHERE 
    ProjID = 20
   ORDER BY ActivityPeriods

This would give you :

| ActivityPeriods |
| --------------- |
| 2018-01-01      |
| 2018-01-02      |
| 2018-01-03      |
| 2018-01-04      |
| 2018-02-05      |
| 2018-02-06      |
| 2018-02-07      |
| 2018-02-20      |
| 2018-02-21      |
| 2018-02-22      |

Fiddle Demo

List all dates within some periods

1 Answers1