select the lists of date between from date and to date and union with result table

Question

I have a result table.

I want to union this result table with list of dates .I wanted to know how to find the lists of date between from date and to date for union operation. for example,if from date is '2022-06-10' and to date is '2022-06-17'.I wanted to find the list of date between from and to .If there is no data in the result for specific date set the total to 0.My expected output.

My current result query

    SELECT sum(q.amount) as total,date(q.created_date) as date FROM quotation q
    where q.created_date <= '2022-06-10'
    and q.created_date >= '2022-06-17'
    group by DATE_FORMAT(q.created_date, "%d/%m/%Y")
union 
(select 0 as total,date(DATE_SUB(CURDATE(), INTERVAL 1 DAY) ))
order by DATE_FORMAT(date,"%d/%m/%Y");

The result

Search for mysql generate dates between 2 dates eg https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql , https://stackoverflow.com/questions/2157282/generate-days-from-date-range — P.Salmon, Jun 16 '22 at 06:40
I am confused by the question 'for example,if from date is '2022-06-10' and to date is '2022-06-17'' - which suggest you supply the date range to the query and your query which suggests you wish to use now() - which is it you want? — P.Salmon, Jun 16 '22 at 06:42

score 0 · Accepted Answer · answered Jun 16 '22 at 07:10

If you want so sum by day for the last 7 days from now and have version 8 or above

DROP TABLE IF EXISTS T;
create table t (dt date, val int);
insert into t values
('2022-06-15',10),('2022-06-15',10),('2022-06-14',10),('2022-06-14',10),('2022-06-14',10);


WITH recursive Date_Ranges AS (
    select DATE(NOW()) as Date
   union all
   select Date - interval 1 day
   from Date_Ranges
   where Date > DATE(NOW()) - INTERVAL 7 DAY)
select date_ranges.date, coalesce(sum(t.val),0) sumval
from Date_Ranges
left join t on t.dt = date_ranges.date
group by date_ranges.date
order by date_ranges.date desc;


+------------+--------+
| date       | sumval |
+------------+--------+
| 2022-06-16 |      0 |
| 2022-06-15 |     20 |
| 2022-06-14 |     30 |
| 2022-06-13 |      0 |
| 2022-06-12 |      0 |
| 2022-06-11 |      0 |
| 2022-06-10 |      0 |
| 2022-06-09 |      0 |
+------------+--------+
8 rows in set (0.002 sec)

select the lists of date between from date and to date and union with result table

1 Answers1