On Thu, 31 Jan 2002, DL Neil wrote:

> Garry,
> I regret to say that I can't see an immediate answer.
> > CREATE TABLE breeding (
> >   sire SMALLINT(4) UNSIGNED,
> >   paired DATE,
> >   split DATE,
> >   num_offspring TINYINT(1) ZEROFILL,
> >   PRIMARY KEY (rec)
> > );
> >
> > >
> > First, we define if a pair was breeding for a given date by seeing if that
> > date lies between the paired date and the split date. In mysql:
> >
> > SELECT FROM breeding WHERE TO_DAYS(paired) <= TO_DAYS('$given_date') AND
> > TO_DAYS('$given_date') <= TO_DAYS(split);
> There is no need to use TO_DAYS() because paired and split are both
> dates so you can operate on them directly. I recommend that you take a
> look at the manual's section on Time and Date Functions.

You are right. I works without the TO_DAYS(). But I did get them from the 
manual :-)

> > Where it gets messy is this has to be put into a loop, between the given
> > start and end dates (year and month only). For each day in the loop, I
> > have to do a query and count the number returned to php. Then average that
> > figure for the month.
> So if I understand correctly, you are thinking in terms of a 'result
> table' which would (ideally) have the days 1-28/29/30/31 in the
> left-hand column, and the number of pairs who were put together on
> that day (paired <= day/date <= split). Then at the bottom you want to
> sum the right-hand column and divide it by the number of days in the
> month to give some sort of average mating possibilities per day. Does
> that sound right?

Basically for statistical reasons I need average pairs per month, just a 
single number. But the months/years to be queried on would be input via a 
form. I was thinking of generating the data on-the-fly, but as you suggest 
above and from something Frank Flynn suggested, I would be better off 
having a separate table with monthly results. They could all be 
pre-calculated except the current month.

> > Normally start and end dates would be input via a form.
> I don't understand, at first you talked of "$given_date and now we
> have two dates! Are you inputting a month - asking for the average for
> January for example. Or are you putting in two dates representing a
> period of arbitrary length and asking for an average of that, eg 42
> days? Please clarify.

Input is two dates, granularity one month. For example start Jan 2000,
finish Dec 2001. Then for each month in that period, I want the avarage
for each month. For example:

2000  January   56.2
2000  February  34.4
2001  December  48.6

There will be a whole lot of other statistics as well, such as number of 
eggs laid, number hatched, number of surviving chicks etc. But I can 
calculate _them_ quite easily.

> If I have followed your explanation, and repeated your logic, then we
> would indeed need to step through the days. However this is not the
> 'relational way' - there may be another way to achieve the 'numbers'.
> Do you actually want/use the daily figures or is it only the
> bottom/end-of-month average that is important?

End of month averages only. 

> Trouble is it's after midnight here. Apologies. Please would you
> respond to the questions above, and if no one else steps in meantime,
> I'll endeavor to return with a more lucid analysis in the morning...

Sorry to keep you awake :-)

Thanks for your help,


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to