Frank,
As yet no response from Garry, but I wanted to ask:
You already have DateTable set up (presumably), so extending its application into a
relationship with the
breeding table is no big deal. Given that one suggestion was that Garry could consider
storing monthly
statistics in another table (from breeding), and thus saves processing each time/only
needs to update once per
month and presumably only for the month just passed; wouldn't it make sense FOR HIM to
combine the two
functions? Initially create the MonthlyStats table to contain only year and month data
(which can be related to
'drive' date-oriented SELECTing), and then update/populate each month's row as and
when the stats become
available. It might also offer opportunities to simplify the processing outlined below.
Your thoughts?
=dn
----- Original Message -----
From: "Frank Flynn" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: 30 January 2002 17:05
Subject: [PHP-DB] Re: Date operations.
> Gary,
>
> You have a right to be confused - this problem is short but it is about as
> complex as they get. Still I have a solution for you, also short and you'll
> (hopefully) find it elegant. I found it interesting...
>
> The thing is we will create a Cartesian product on purpose - this is
> something they tell people learning SQL to avoid (and usually they are not
> helpful) but here it is the only to get a daily average since you don't have
> daily records of your pairs just a start and stop date.
>
> I was looking at the problem from the child point of view in my first post
> (easier since children only have a single birthday). I'll use your problem
> now.
>
> We will keep the DateTable and use your 'breeding' table but I will change
> the 'paired' and 'split' columns to int (you don't need to do this but it
> will make thing easier - some DBMS's will always put a time stamp in a Date
> field and this does screw thing up - it makes it harder to join on them).
>
> So we have:
>
> breeding
> -----------
> rec SMALLINT PRIMARY KEY
> sire SMALLINT
> dam SMALLINT
> paired INT
> split INT
> num_offspring INT
>
> And
>
> DateTable
> ------------
> dayID INT PRIMARY KEY
> myDate DATE
> month INT
> year INT
> quarter INT
>
> /* and as discussed any other date related columns */
>
>
> > I need to count how many pairs were breeding
> > on a given day, and then average over the month.
>
> OK - fasten you seat belt :-0
>
> SELECT count(*), myDate
> FROM breeding, DateTable
> WHERE paired >= dayID
> AND split <= dayID
> AND month = 2
> AND year = 2001
> GROUP BY myDay
> ORDER BY myDay
>
> /* you could have just as easily used a start and stop date */
> /* myDay >= "2/1/2001" and myDate < "3/1/2001" */
>
> This will give you output something like this (the total per day):
> 5 Feb 1, 2001
> 5 Feb 2, 2001
> 5 Feb 3, 2001
> 5 Feb 4, 2001
> 5 Feb 5, 2001
> 6 Feb 6, 2001
> 6 Feb 7, 2001
> 6 Feb 8, 2001
> 6 Feb 9, 2001
> 6 Feb 10, 2001
> 6 Feb 11, 2001
> ... And so on ...
>
>
> To get the average per month I'd use a temporary table (a table you create
> just for this report or that is always there but empty except during this
> report)
>
> /* start with an empty table */
> CREATE TABLE TotalByDay
> ( total INT,
> dayID INT)
>
> /* --- or --- */
>
> TRUNCATE TABLE TotalByDay
>
> /* now fill it... */
> /* notice it the same SQL but I've gone back to */
> /* dayID (the INT) not the full date and I've done */
> /* the whole year so we can average per month */
>
> INSERT INTO TotalByDay
> SELECT count(*), dayID
> FROM breeding, DateTable
> WHERE paired >= dayID
> AND split <= dayID
> AND year = 2001
> GROUP BY dayID
> ORDER BY dayID
>
>
> /* OK almost there ... Now to average by month */
>
> SELECT ave(total), month, year
> FROM TotalByDay, DateTable
> WHERE TotalByDay.dayID = DateTable.dayID
> GROUP BY month
> ORDER BY month
>
>
> Now you've got output like
>
> 5 1 2001
> 6 2 2001
> 6 3 2001
> 7 4 2001
> 12 5 2001
> 11 6 2001
> 11 7 2001
> 7 8 2001
> 7 9 2001
> 9 10 2001
> 9 11 2001
> 9 12 2001
>
> From here you convert the month INT back into a name.
>
> Feel free to writ back with any questions (it's headdy stuff) also I don't
> have a mySQL db handy to check the syntax against - hopefully I've
> remembered everything correctly. The concept is sound, we do this all the
> time.
>
> The "got ya's" are the DateTable must be full (don't fill it to the end of
> this year and forget about it - next year your reports will be wrong)
>
> Frank
>
>
> >
> > Ouch, I'm getting a bit lost. I need to count how many pairs were breeding
> > on a given day, and then average over the month. Playing with mysql I get
> > for say the 1st day of the year:
> >
> > select count(*) from breeding
> > where TO_DAYS(paired) <= TO_DAYS('2002-1-1')
> > AND TO_DAYS('2002-1-1') <= TO_DAYS(split);
> >
> > That will give me the number of pairs breeding that day. Then I would have
> > to loop through the year and keep track of number of pairs, days of months
> > etc to get the monthly averages.
> >
> > Can you suggest a DateTable format that could help me? The start and end
> > dates for the query would be input via a form. The granularity would be
> > one month, but the details need to be averaged on a daily basis.
> >
> > 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]
>
>
--
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]
