List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:October 20 2004 2:39pm
Subject:Re: SELECTING Non existing Dates
View as plain text  
In article <OF06C4447F.ABD474E2-ON85256F33.0048954F-85256F33.0049AD45@stripped>,
SGreen@stripped writes:

> With a LEFT JOIN you will get back your "zero hours" only if there exist 
> records in the Timesheets table that match no records in the projects 
> table.  If you have no Timesheets data for the 23rd or 24th, then you 
> can't have any rows in your results for those dates.  MySQL won't fill in 
> the missing dates for you.

> I have had to resort to writing a script that builds a temporary table 
> that contains a complete range of the dates I need for a query (the 
> results are to be used to make a graph) just so that I can have blank 
> values for those dates where no data exists.  Others on the list may have 
> better ideas on how to fill in missing data (like your missing dates) so I 
> only offer my idea as a suggestion, not a solution.

I'm using roughly the same technique, with a small variation: my
helper table is not temporary, but permanent, and contains just the
integers 1 .. 1000:

  CREATE TABLE seq (val INT PRIMARY KEY);
  INSERT INTO seq VALUES (1), (2), ..., (1000);

(You could use any range of values you need.)

The join now looks like this:

  SELECT '2004-10-17' + INTERVAL s.val DAY AS day,
         T.Hours, P.Project_Name
  FROM utildb.seq s
  LEFT JOIN Timesheets T ON T.Date = '2004-10-17' + INTERVAL s.val DAY
  LEFT JOIN Projects P ON P.Project_ID = T.Project_ID
  WHERE s.val BETWEEN 1 AND 7
    AND T.User_ID = "1"
    AND T.Type = "Project"
  GROUP BY day, T.Project_ID
  ORDER BY day

The disadvantage is that the join expression is more complicated, but
I don't have to mess with temp tables.

Thread
SELECTING Non existing Datesshaun thornburgh20 Oct
  • Re: SELECTING Non existing DatesMartijn Tonies20 Oct
  • Re: SELECTING Non existing DatesSGreen20 Oct
  • Re: SELECTING Non existing DatesHarald Fuchs20 Oct
Re: SELECTING Non existing Datesshaun thornburgh21 Oct