SQL query for last 365 days report

Question

I have a reports table with the following structure :

I want a SQL Query to get the report for the last 365 days by following conditions :

Group dates if the same date is repeated.
The days which the report is not available for the last 365 days, I need those days added to the result rows with 0 as their success and failed recipients.

I tried to get it by group by report dates

SELECT report_date, SUM(success_recipient) as success_recipient, SUM(failed_recipient) as failed_recipient FROM reports GROUP BY report_date;

and I have got the grouped result which satisfies the first condition

Now I need to append the rest of the days in the last 365 days to this result in which 0 as their success and failure recipients.

Expected result :

and so on ..

MYSQL VERSION : 5.6

*The days which the report is not available for the last 365 days, I need those days added to the result rows with 0 as their success and failed recipients.* You must generate base calendar table which contains all dates within the period in interest and join your data to it. — Akina, Nov 29 '21 at 07:20
Do any of these help: https://stackoverflow.com/questions/2157282/generate-days-from-date-range https://stackoverflow.com/questions/14105018/generating-a-series-of-dates https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — Rwd, Nov 29 '21 at 07:24

score 0 · Answer 1 · answered Nov 29 '21 at 07:48

0

One way to achieve this is using "with recursive" to generate all dates you need in you output and then outer join to the rest of your query. Note: I use the number 356 as it is in your description but it seems more appropriate to use date difference as this approach does not take into account leap years. Using the query below you will get NULL values in case you have no data. If you need the value 0 you can use coalesce(sum(...), 0).

with recursive
dates as (
  select curdate()-356 dt
  union all
  select dt+1 from dates
  where dt < curdate()
)
select 
  dt report_date,
  sum(success_recipient) success_recipient, 
  sum(failed_recipient) failed_recipient
from dates
left join reports on report_date = dt
group by report_date;

answered Nov 29 '21 at 07:48

Slavian Petrov

612
4
6

I guess "with recursive" is there only from MySQL 8. It says, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive dates as (select curdate()-356 dt union all select dt+1 from dates whe' at line 1. " – Ammar Abdul Aziz Nov 29 '21 at 08:08
Yes, it is. What is your target version? – Slavian Petrov Nov 29 '21 at 08:19
I am using version 5.6 for my project. – Ammar Abdul Aziz Nov 29 '21 at 08:21
And you are right, I meant date difference (1 year) instead of 365 days. – Ammar Abdul Aziz Nov 29 '21 at 08:22
In MySQL 5.6 there are no CTE. I think you will need a temporary table that you will prefill by all the necessary dates, i.e. replace the dates in the sql above, and then outer join to your other query – Slavian Petrov Nov 29 '21 at 08:30

score 0 · Accepted Answer · answered Dec 04 '21 at 09:37

From the above comments and the answer, I could write this query which gave me the expected outcome :

SELECT a.date, SUM(COALESCE(r.success_recipient, 0)), SUM(COALESCE(r.failed_recipient, 0))
FROM (
    SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY AS date
    FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
LEFT JOIN reports r ON a.date = r.report_date 
WHERE a.date between DATE_SUB(CURDATE(), INTERVAL 1 YEAR) and now() 
GROUP BY a.date;

SQL query for last 365 days report

2 Answers2