Skip to content
Advertisement

SQL query for last 365 days report

I have a reports table with the following structure :

enter image description here

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

  1. Group dates if the same date is repeated.
  2. 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

enter image description here

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 :

enter image description here and so on ..

MYSQL VERSION : 5.6

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement