From: Peter Brawley
Date: February 3 2006 5:25pm
Subject: Re: problem with excluded days in date range query.
List-Archive: http://lists.mysql.com/mysql/194573
Message-Id: <43E3921D.7060109@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Paul,

>If there are no data for a particular day, it is not included. Is
>there a way to include all days even if the result is 0? I saw an
>example that included another table with all dates and an inner join
>but that seems a little clunky.


In SQL, enumerating data you don't have requires some kind of join with 
data you do have. A calendar table is very likely your most efficient 
solution. It'll also facilitate handling of non-working days &c.

PB



Paul Halliday wrote:
> I have the following query:
>
> select count(*) as cnt, date(timestamp) as day from table where
> date_sub(curdate(),interval 14 day) <= timestamp group by day;
>
> If there are no data for a particular day, it is not included. Is
> there a way to include all days even if the result is 0? I saw an
> example that included another table with all dates and an inner join
> but that seems a little clunky.
>
> Thanks.
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006