List:General Discussion« Previous MessageNext Message »
From:Jonathan Mangin Date:January 4 2006 8:20pm
Subject:Re: Calendar table workaround
View as plain text  
RE: Calendar table workaround>
>A table of dates to which to join other tables,
>ensuring reports that reflect days for which no
>data is available.

I forget the query but I know it can be done.  But can't you just have a table (called
calendar?) with each entry having it's own row with a date column that gives whatever
date you need in it and then other columns for any other details you need to have to go
along with the date. 

-----
Sorry, I thought this was a very common situation.  And, therefore,
instantly recognizable.  I'll include the full story.

my $bdate = '2005-08-01';
my $edate = '2005-08-14';
my $uid = 'george';
my $temp_tbl = 'calendar_' . $uid;

my $sth = $dbh->prepare("
create table $temp_tbl
(date date,
uid varchar(14))
engine = memory
select date,
? as uid
from calendar
where date between ? and ?");
$sth->execute($uid, $bdate, $edate);

$sth = $dbh->prepare("
(select
$temp_tbl.date as date,
concat(type,seq) as event,
time_format(time,'%H:%i'),
value as val1,
'' as val2
from $temp_tbl
left join table1
on table1.date = $temp_tbl.date
where $temp_tbl.uid = ?
and $temp_tbl.date between ? and ?)
union
(select
$temp_tbl.date,
concat(type,seq),
time_format(time,'%H:%i'),
t1_val,
t2_val
from $temp_tbl
left join table2
on table2.date = $temp_tbl.date
where $temp_tbl.uid = ?
and $temp_tbl.date between ? and ?)
order by date, event");
$sth->execute($uid, $bdate, $edate, $uid, $bdate, $edate);

These are $uid-specific reports (where .uid = ?) and
uid, of course, doesn't exist in my standard 'calendar table.'

The question:  Is creating another "temporary" table (that does
include both date and uid) the best thing to do here?

Thanks.


Thread
Calendar table workaroundJonathan Mangin4 Jan
  • Re: Calendar table workaroundJonathan Mangin4 Jan
    • Re: Calendar table workaroundPeter Brawley4 Jan
      • SELECT DISTINCT uses index but is still slowJames Harvard4 Jan
        • Inconsistent rows returned & examined in slow query logJames Harvard4 Jan
  • RE: Calendar table workaroundJay Paulson \(CE CEN\)4 Jan
  • Re: Calendar table workaroundJonathan Mangin4 Jan
  • Re: SELECT DISTINCT uses index but is still slowC.R.Vegelin5 Jan
    • Re: SELECT DISTINCT uses index but is still slowJames Harvard5 Jan