-2

I have a table with following entries

Created at tax
2020-01-05 300
2020-06-10 350
2020-09-15 400
2020-09-28 320

If I try to get data between 2020-02-01 to 2020-10-30 I need to get output as following

Created at tax
2020-02-01 300
2020-03-01 300
2020-04-01 300
2020-05-01 300
2020-06-01 300
2020-06-10 350
2020-07-01 350
2020-08-01 350
2020-09-01 350
2020-09-15 400
2020-09-28 320
2020-10-01 320
ayush poudel
  • 1
  • 2
  • 1
    Does this answer your question? [mysql generate missing dates with previous value](https://stackoverflow.com/questions/56383199/mysql-generate-missing-dates-with-previous-value) – sticky bit Dec 25 '20 at 06:07
  • And this: https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range – sticky bit Dec 25 '20 at 06:08
  • It does but partially. If from date doesn't have data, then I need to get data from last entered date (if exists) and output it as a from date and so on. – ayush poudel Dec 25 '20 at 06:42
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Dec 25 '20 at 08:14
  • There is no coding attempt included in this "requirements dump". – mickmackusa Dec 29 '20 at 21:47

1 Answers1

0

In MySQl 8 you can generate the missing records recursively. Otherwise, a temporary table is required.

WITH RECURSIVE dates (created_at) AS (
  SELECT '2020-02-01' as created_at
  UNION ALL
  SELECT DATE_ADD(created_at, INTERVAL 1 MONTH)
  FROM dates WHERE created_at <= '2020-10-30'
), result_table AS (
  SELECT created_at, tax FROM taxes
  UNION 
  SELECT created_at, (
      SELECT tax FROM taxes t 
      WHERE t.created_at <= d.created_at 
      ORDER BY t.created_at DESC LIMIT 1
    ) AS tax
  FROM dates d
)   
SELECT * FROM result_table
WHERE created_at BETWEEN '2020-02-01' AND '2020-10-30'
ORDER BY 1

db<>fiddle

id'7238
  • 2,428
  • 1
  • 3
  • 11
  • Can you suggest me way of doing same query using temporary table. – ayush poudel Dec 28 '20 at 05:40
  • Look at this [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d61e18d27c8300b3a9ff4c33b5ebe512). There are many other ways to create a temporary table specifically for this task. For example, by creating a table and inserting records, creating a stored procedure, or even building an entire SQL statement with fiddle's temp part named `months` using PHP (as you tagged the question with `php` tag). All of this applies if the MySQL version is lower than 8. – id'7238 Dec 28 '20 at 06:32