mysql query with conditional date range

[mbarandao]

Hello all:

I have the following mysql query statement:

SELECT COUNT(*) as ttl_rows FROM (SELECT DISTINCT(date(timeentry)) AS day, COUNT(*) AS total FROM history where MONTH(CAST(timeentry as date)) = MONTH(NOW()) AND YEAR(CAST(timeentry as date)) = YEAR(NOW()) GROUP BY DATE_FORMAT(timeentry, '%d') ASC) as ttl_row

the statement performs a search for all records entered for the current month and thus would produced the following:

day     total
2012-12-01   1
2012-12-02   6
2012-12-04 4
2012-12-05   3

As you can see, there is no record for 2012-12-03. My interest here is to modify the query to return all dates with the date range and where there is no entry, insert 0. So essentially, my return would be something like:

day     total
2012-12-01   1
2012-12-02   6
**2012-12-03     0**
2012-12-04 4
2012-12-05   3

I hope this is achievable and that anyone here may be able to assist.

Thanks
Mossa

[pritaeas]

Your best bet would be to use an additional table with all dates, and join with that.

[mbarandao]

Pritaeas, thanks for your thoughts on the question.

I'm a bit curious on how I would achieve your suggestion. I would appreciate an example of how to populate this additional table with dynamic calendar dates (in the form of: yyyy/mm/dd) as well as how the join would be constructed.

[pritaeas]

Just use some scripting/programming language to fill the new table. Then just left join on day, using IFNULL to add zeroes.

[mbarandao]

Ok, I have created a separate table with dates

CREATE TABLE calendar (
theDate DATE PRIMARY KEY
);

and constructed the following sql statement

SELECT C.theDate, IFNULL( count(*), 0 ) AS countForDate
FROM calendar AS C LEFT JOIN history AS H
ON C.theDate = DATE( CAST( H.timeentry AS DATE ) )
WHERE YEAR(C.theDate) = YEAR(NOW()) AND MONTH(C.theDate) = MONTH(NOW()) and
C.theDate BETWEEN '2012-12-1' AND '2012-12-07'
GROUP BY C.theDate
ORDER BY C.theDate 

However, it seems to be working fine with the exception of the returning of the "0" where the record is null on the history table. Instead of the zero, it returns 1. Any thought?

with the above query, the following is outputted:

theDate countForDate
2012-12-01  1
2012-12-02  6
2012-12-03  1
2012-12-04  4
2012-12-05  3
2012-12-06  3
2012-12-07  1

but, the correct output should be:

theDate countForDate
2012-12-01  1
2012-12-02  6
**2012-12-03    0**
2012-12-04  4
2012-12-05  3
2012-12-06  3
**2012-12-07    0**

The following dates: 2012-12-03 and 2012-12-07 have no entry so the query should return 0 instead of 1.

Any thoughts on how to address this problem?

Best,
Mossa

[urtrivedi]

SELECT C.theDate, IFNULL( sum(case when DATE( CAST( H.timeentry AS DATE ) ) is null then 0 else 1 end), 0 ) AS countForDate
FROM calendar AS C LEFT JOIN history AS H
ON C.theDate = DATE( CAST( H.timeentry AS DATE ) )
WHERE YEAR(C.theDate) = YEAR(NOW()) AND MONTH(C.theDate) = MONTH(NOW()) and
C.theDate BETWEEN '2012-12-1' AND '2012-12-07'
GROUP BY C.theDate
ORDER BY C.theDate 

[mbarandao]

urtrivedi, that did the trick! I really appreciate your assistance with this.

The very best
Mossa