Getting all days for a month

[jacob21]

Hi,
Need suggestion for a query.
I am trying to get count(c3.id) for all days of feb.
As in cs.created_at some dates are missing.How can i use below clause.
group by day(cs.created_at)
something like group by day(month(cs.created_at)) not working..

select 
count(case when cs.id IN(select max(id) from complaint3_status where complaint_id=c3.id  and status_type=10) and cs.status_value=11   then c3.id end) as resolved_complaints
from 
complaint3 c3 , complaint3_status cs, company c,complaint3_details cd,user3 u3
where
c3.id=cs.complaint_id
and cd.complaint_id=c3.id
and u3.id=c3.user_id
and c.id=c3.company_id
and c3.company_id=852
and month(cs.created_at)=02
and year(cs.created_at)=2014
**group by day(cs.created_at)**

[pritaeas]

IMO the only sure way to do something like this is to generate a table containing a date for each day of the year.

[jacob21]

Hi,
I am storing created_at like 2014-03-03T14:36:35.000Z.
I tried a proc but is also not working, giving o count for each date.
may be due to date format.
Need suggestion for proc

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `days_of_month`(iDate DATETIME)
    DETERMINISTIC
BEGIN

    DECLARE last_day,mm,yy,dd INT DEFAULT 0;
    SET dd = 1;
    SET mm = month(iDate);
    SET yy = year(iDate);

    set iDate = case when iDate is null then now() else iDate end;
    SET last_day = date_format(LAST_DAY(iDate),'%d');

    DROP TABLE IF EXISTS `days_of_month_tblTemp`;
    CREATE TEMPORARY TABLE days_of_month_tblTemp(monthDates DATE);

    label1: LOOP
         insert into days_of_month_tblTemp(monthDates) values (concat(yy,'-',mm,'-',dd));
         SET dd = dd + 1;
        IF dd < (last_day+1) THEN ITERATE label1; END IF;
         LEAVE label1;
      END LOOP label1;

    SELECT dmt.monthDates,count(c3.id) from  days_of_month_tblTemp dmt
    LEFT JOIN complaint3 c3 ON c3.created_at=dmt.monthDates group by dmt.monthDates;


END

[Alberto Bucur]

Hi,
Try this:

select s.created_at, count(s.id)
    from
        (select c3.id id, cs.created_at created_at
            from complaint3 c3, complaint3_status cs
            where
                c3.id = cs.complaint_id and
                c3.company_id = 852 and
                month(cs.created_at)=02 and
                year(cs.created_at)=2014
         ) s
     group by s.created_at

[jacob21]

Thanks albucurus,but its not working.I want all days of month.
As in cs.created_at some dates are missing

[Alberto Bucur]

Try this:
(I put just first 6 day of the month)

select monthdays.countday as day, count(s.id)
    from
    (select c3.id id, cs.created_at created_at
    from complaint3 c3, complaint3_status cs
    where
    c3.id = cs.complaint_id and
    c3.company_id = 852 and
    month(cs.created_at)=02 and
    year(cs.created_at)=2014
    ) s,
    (SELECT 1 as countday UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 ) monthdays
    where monthdays.countday = day(s.created_at)
    group by monthdays.countday

[Alberto Bucur]

An other way is to make a calandar table with all days of the month and insert in this table all counts for each day.