From: Peter Brawley Date: January 4 2006 7:48pm Subject: Re: Calendar table workaround List-Archive: http://lists.mysql.com/mysql/193505 Message-Id: <43BC269F.6020006@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43BC269F72A9=======" --=======AVGMAIL-43BC269F72A9======= Content-Type: multipart/alternative; boundary=------------000105050705090304060708 --------------000105050705090304060708 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jonathan, I understand what you mean by a calendar table, but like Rhino I've no idea what you're asking. How to link the calendar table to other tables depends on your other tables. For a fairly simple & generic example of a calendar table see http://www.artfulsoftware.com/queries.php#20. PB ----- Jonathan Mangin wrote: >----- Original Message ----- >From: "Rhino" >To: "Jonathan Mangin" >Sent: Wednesday, January 04, 2006 1:25 PM >Subject: Re: Calendar table workaround > > > > >>----- Original Message ----- >>From: "Jonathan Mangin" >>To: >>Sent: Wednesday, January 04, 2006 10:45 AM >>Subject: Calendar table workaround >> >> >> >> >>>I created a calendar table (date only), but all >>>where clauses include a uid. Is the following a >>>sane workaround to get a usable calendar table? >>>Anything else I can do? >>> >>>my $sth = $dbh->prepare(" >>>create table $temp_tbl >>>(date date, >>>uid varchar(14)) >>>select date, >>>? as uid >>>from calendar >>>where date between ? and ?"); >>>$sth->execute($uid, $bdate, $edate); >>> >>> >>> >>I have no idea what you are asking, which may explain why no one has >> >> >replied > > >>to your question yet. >> >>I've been working with relational databases for 20 years and I've never >>heard the term "calendar table". What are you trying to accomplish? If you >>describe clearly what you are trying to do, perhaps someone can help you >>devise a way to do it in MySQL. >> >>Rhino >> >> >> >> >A table of dates to which to join other tables, >ensuring reports that reflect days for which no >data is available. > > > > --------------000105050705090304060708 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Jonathan,

I understand what you mean by a calendar table, but like Rhino I've no idea
what you're asking. How to link the calendar table to other tables depends on
your other tables. For a fairly simple & generic example of a calendar table see http://www.artfulsoftware.com/queries.php#20.

PB

-----

Jonathan Mangin wrote:

----- Original Message ----- 
From: "Rhino" <rhino1@stripped>
To: "Jonathan Mangin" <jon.mangin@stripped>
Sent: Wednesday, January 04, 2006 1:25 PM
Subject: Re: Calendar table workaround

----- Original Message ----- 
From: "Jonathan Mangin" <jon.mangin@stripped>
To: <mysql@stripped>
Sent: Wednesday, January 04, 2006 10:45 AM
Subject: Calendar table workaround

I created a calendar table (date only), but all
where clauses include a uid.  Is the following a
sane workaround to get a usable calendar table?
Anything else I can do?

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

I have no idea what you are asking, which may explain why no one has

replied

to your question yet.

I've been working with relational databases for 20 years and I've never
heard the term "calendar table". What are you trying to accomplish? If you
describe clearly what you are trying to do, perhaps someone can help you
devise a way to do it in MySQL.

Rhino

A table of dates to which to join other tables,
ensuring reports that reflect days for which no
data is available.

--------------000105050705090304060708-- --=======AVGMAIL-43BC269F72A9======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.12/220 - Release Date: 1/3/2006 --=======AVGMAIL-43BC269F72A9=======--