List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 3 2006 5:25pm
Subject:Re: problem with excluded days in date range query.
View as plain text  
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

Thread
problem with excluded days in date range query.Paul Halliday3 Feb
  • Re: problem with excluded days in date range query.sheeri kritzer3 Feb
  • Re: problem with excluded days in date range query.Peter Brawley3 Feb