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.
