Create a row for every day in a date range?

Question

I have a table like this:

+----+---------+------------+
| id |  price  |    date    |
+----+---------+------------+
| 1  | 340     | 2018-09-02 |
| 2  | 325     | 2018-09-05 |
| 3  | 358     | 2018-09-08 |
+----+---------+------------+

And I need to make a view which has a row for every day. Something like this:

+----+---------+------------+
| id |  price  |    date    |
+----+---------+------------+
| 1  | 340     | 2018-09-02 |
| 1  | 340     | 2018-09-03 |
| 1  | 340     | 2018-09-04 |
| 2  | 325     | 2018-09-05 |
| 2  | 325     | 2018-09-06 |
| 2  | 325     | 2018-09-07 |
| 3  | 358     | 2018-09-08 |
+----+---------+------------+

I can do that using PHP with a loop (foreach) and making a temp variable which holds the previous price til there is a new date.

But I need to make a view ... So I should do that using pure-SQL .. Any idea how can I do that?

Wait why are the id's in the expected results different then the source data? Does it need to be a row number based on ton the date order? — Raymond Nijland, Feb 12 '19 at 13:45
@RaymondNijland well I don't care about ids .. it would be better to not be duplicate. — Martin AJ, Feb 12 '19 at 13:45
"well I don't care about ids .. it would be better to not be duplicate" right that would make it a dense rank instead of a rank. — Raymond Nijland, Feb 12 '19 at 13:47
Create a calendar table, or cte, having all dates of interest. OUTER JOIN. — jarlh, Feb 12 '19 at 13:47
"Create a calendar table, or cte, having all dates of interest. OUTER JOIN" @jarlh means a LEFT OUTER JOIN — Raymond Nijland, Feb 12 '19 at 13:48
similar: https://dba.stackexchange.com/questions/166024/how-to-create-a-row-for-every-day-in-a-date-range-using-a-stored-procedure — tanaydin, Feb 12 '19 at 13:48
also similar: https://stackoverflow.com/questions/27489564/for-each-day-between-two-dates-add-a-row-with-the-same-info-but-only-that-day-i — tanaydin, Feb 12 '19 at 13:49
similar: https://dba.stackexchange.com/questions/136422/insert-all-dates-from-a-month-into-a-table — tanaydin, Feb 12 '19 at 13:49
@Strawberry Then you better just downvote the question then! — Tim Biegeleisen, Feb 12 '19 at 13:49
@tanaydin this question is tagged MySQL not SQL Server (MSSQL), so the first two comments should be ideally removed — Raymond Nijland, Feb 12 '19 at 13:51
Consider handling issues of data display in application code. If you must have an sql solution, then you know the steps that I consider should be taken in the first instance - and I would observe that your acceptance rate for previous questions remains desultory. — Strawberry, Feb 12 '19 at 14:12

score 5 · Accepted Answer · answered Feb 12 '19 at 14:01

5

You could use a recursive CTE to generate the records in the "gaps". To avoid that an infinite gap after the last date is "filled", first get the maximum date in the source data and make sure not to bypass that date in the recursion.

I have called your table tbl:

with recursive cte as (
    select     id, 
               price, 
               date, 
               (select max(date) date from tbl) mx
    from       tbl
    union all
    select     cte.id,
               cte.price, 
               date_add(cte.date, interval 1 day),
               cte.mx
    from       cte
    left join  tbl
           on  tbl.date = date_add(cte.date, interval 1 day)
    where      tbl.id is null
           and cte.date <> cte.mx
)
select   id, 
         price, 
         date
from     cte
order by 3;

demo with mysql 8

answered Feb 12 '19 at 14:01

trincot

317,000
35
244
286

Thank you very much .. Just two questions, what does `CTE` stands for? *(google believe `Chronic Traumatic Encephalopathy` which makes no sense in this case)*. And what does `with` do in MySQL? – Martin AJ Feb 12 '19 at 14:06
2

["Common Table Expression"](https://mariadb.com/kb/en/library/with/) -- and that is indeed what follows a `with` keyword. NB: I am not sure your version of MariaDB supports `with recursive` syntax. I hope it does... – trincot Feb 12 '19 at 14:07
Can you please explain what's that `3` in `order by` clause? How would it be for another dataset (more that 3 rows)? – Martin AJ Feb 13 '19 at 07:30
2

It is just the number of the column to order by (`date`). Not related to rows. – trincot Feb 13 '19 at 07:33

Tim Biegeleisen · Answer 2 · 2019-02-12T14:20:56.337

Here is an approach which should work without analytic functions. This answer uses a calendar table join approach. The first CTE below is the base table on which the rest of the query is based. We use a correlated subquery to find the most recent date earlier than the current date in the CTE which has a non NULL price. This is the basis for finding out what the id and price values should be for those dates coming in from the calendar table which do not appear in the original data set.

WITH cte AS (    
    SELECT cal.date, t.price, t.id
    FROM
    (
        SELECT '2018-09-02' AS date UNION ALL
        SELECT '2018-09-03' UNION ALL
        SELECT '2018-09-04' UNION ALL
        SELECT '2018-09-05' UNION ALL
        SELECT '2018-09-06' UNION ALL
        SELECT '2018-09-07' UNION ALL
        SELECT '2018-09-08'
    ) cal
    LEFT JOIN yourTable t
        ON cal.date = t.date
),
cte2 AS (
    SELECT
        t1.date,
        t1.price,
        t1.id,
        (SELECT MAX(t2.date) FROM cte t2
         WHERE t2.date <= t1.date AND t2.price IS NOT NULL) AS nearest_date
    FROM cte t1
)

SELECT
    (SELECT t2.id FROM yourTable t2 WHERE t2.date = t1.nearest_date) id,
    (SELECT t2.price FROM yourTable t2 WHERE t2.date = t1.nearest_date) price,
    t1.date
FROM cte2 t1
ORDER BY
    t1.date;

Demo

Note: To make this work on MySQL versions earlier than 8+, you would need to inline the CTEs above. It would result in verbose code, but, it should still work.

Given that this is CTE, the UNION ALL bit seems a bit redundant. — Strawberry, Feb 12 '19 at 14:14
@Strawberry The Trincot answer will probably get marked, but if someone might want a solution which does not rely on analytic functions or recursion, then my answer is an option. — Tim Biegeleisen, Feb 12 '19 at 14:24

Rick James · Answer 3 · 2019-02-13T03:20:29.913

Since you are using MariaDB, it is rather trivial:

MariaDB [test]> SELECT  '2019-01-01' + INTERVAL seq-1 DAY  FROM seq_1_to_31;
+-----------------------------------+
| '2019-01-01' + INTERVAL seq-1 DAY |
+-----------------------------------+
| 2019-01-01                        |
| 2019-01-02                        |
| 2019-01-03                        |
| 2019-01-04                        |
| 2019-01-05                        |
| 2019-01-06                        |
(etc)

There are variations on this wherein you generate a large range of dates, but then use a WHERE to chop to what you need. And use LEFT JOIN with the sequence 'derived table' on the 'left'.

Use something like the above as a derived table in your query.

Create a row for every day in a date range?

3 Answers3

Demo