I have the same issue (missing dates) with my graphing widget, too. I worked around my problem by using script to create a temp table that contains all of the dates that cover the range of dates I want to chart then left join the data tables to my temp table. Sure it's a small memory load, and there's a bit of overhead involved in generating all of those date values, but it goes away right after I am through with it.
If it becomes a problem, I will probably do as you thought about doing and will either create one long table of nothing but dates or I will massage my data after retrieval or I will change my widget. So far I am not running into any noticeable problems. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Partap Davis <[EMAIL PROTECTED]> wrote on 10/14/2004 01:35:31 AM: > On Wed, 13 Oct 2004 20:25:48 -0700, Chris <[EMAIL PROTECTED]> wrote: > >> create table test_table > >> (d date not null, > >> name char(32) not null, > >> loc integer not null, > >> type integer not null, > >> amount integer, > >> primary key (d, name, loc, type)); > snip snip > > This is how I would do it: > > > > SELECT > > d as day, > > SUM(amount) as total, > > SUM(IF(1=loc,amount,0)) as loc1_total, > > SUM(IF(2=loc,amount,0)) as loc2_total > > FROM test_table > > GROUP BY d > > ; > > > > I'm not sure this is the best way to go, as that SUM(IF()) functionality > > seems a bit unclean to me, but it works. > > > > Thanks, that works great! > > Now, on a somewhat related note, I'm trying to get the data from mysql > in a ready to use format with no post-processing required... > > I'm graphing the data from this query using dates on the x axis. The > input to my graph module (GD::Graph) requires a constant-length list. > So if any days in my selection range have no data, I need to fill the > space with an empy value. > > For example, say my date range is '2004-10-01' to '2004-10-05' > > and the query returns: > day, amount > 2004-10-01, 50 > 2004-10-02, 100 > 2004-10-04, 250 > > I have to do some date manipulation in perl afterward to check for > missing values... > If I could get a query that returned: > day, amount > 2004-10-01, 50 > 2004-10-02, 100 > 2004-10-03, NULL > 2004-10-04, 250 > 2004-10-05, NULL > > That would be so much nicer in some cases. I'm thinking it would be > sort of like the output from a LEFT JOIN if I had a table containing > just a bunch of sequential dates...but I don't...and the dates can > actually be arbitrary, so it would have to be a pretty big table, with > no real data in it. > > Does anyone know of some way to fake this "date table"? (again, I'm > using mysql 4.0.16) > ...stuck on these LEFT JOINS lately for some reason ;-) > > Thanks, > -partap > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
