In this post, we will discuss How to fill in missing dates in range MySQL for which we need to combine a subquery and union statement where we use the subquery to generate the list of all the lists of required ranges as we want and the union statement for combining the exported list with that of original data filling in all the missing values with NULL.
Filling Missing Dates
Here we will take an example where we have stable with some dates skipping some dates in between and we are going to give the range of dates and it will all the dates and corresponding data will be assigned as NULL. For example, we have a table as followed.
sales +------------+-------+ | sale_ date | amount| +------------+-------+ | '2022-03-01' | 100 | | '2022-03-03' | 200 | | '2022-03-06' | 150 | +------------+-------+
So here we are missing some of the dates where there is no data so we are going to create a date for every day and if the date is not mentioned here we ill assigned the data as null so for the same follow the code given below:
SELECT all_ dates. sale_ date, sales. amount
FROM (
SELECT '2022-03-01' + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS sale_ date
FROM (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS c
) all_dates
LEFT JOIN sales ON all_dates. sale_ date = sales. sale_ date
WHERE all_ dates. sale_ date BETWEEN '2022-03-01' AND '2022-03-06';
In this above-given query, we use a subquery to generate a list of all dates within the desired range. The subquery generates a list of dates using a combination of a UNION statement and a series of SELECT statements that use the INTERVAL function to generate a range of dates. The resulting subquery produces a list of all dates between ‘2022-03-01’ and ‘2022-03-06’.
And then We LEFT JOIN the resulting subquery with the original “sales” table on the “sale_date” column, ensuring that all dates are included in the result set. This will fill in any missing dates with NULL values.
Finally, we use the WHERE clause to filter the results which only include dates within the desired range.
This result after the execution will change the table and the final table will look like as followed:
+------------+-------+ | sale_ date | amount| +------------+-------+ | 2022-03-01 | 100 | | 2022-03-02 | NULL | | 2022-03-03 | 200 | | 2022-03-04 | NULL | | 2022-03-05 | NULL | | 2022-03-06 | 150 | +------------+-------+
To learn more about How to fill missing dates in range MySQL visit: by stack overflow
To learn more about MySQL tutorials and the solutions to the problems along with concepts and tutorials of solutions list and learn visit: MySQL Tutorials And Problems.
