Re: Date operations.

On Wed, 30 Jan 2002, DL Neil wrote: > > Can I request some db-speak please? You already have a tbl (or more). > So let's start by asking for the table definitions The table definition is as follows: 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) ); > > Do you already have some SQL as a 'first attempt'? Could you also post > that, together with a note about its short-comings/what needs to be > added? That would also assist (my, feeble) comprehension. 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); 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. Normally start and end dates would be input via a form. I was wondering if anyone had done that sort of thing, or whether there was a more efficient way of doing it than stepping through the database one day at a time.