On Thu, 31 Jan 2002, DL Neil wrote:
> Garry,
> I regret to say that I can't see an immediate answer.
>
> > CREATE TABLE breeding (
> > rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
> > sire SMALLINT(4) UNSIGNED,
> > dam 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,
Regards,
Garry.
--
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]
