The traditional Data Warehouse way to solve this is to have a second table
of dates. The table might look like:
DateTable
-----------
dayID int,
myDate datetime
And you add all the particular columns that you might ever want to search
on, like:
month int,
year int,
dayOfYear, int
quarter int
And then your table(s) only include the dayID (the integer - perhaps the
date too but it's not necessary, all your searching and grouping is done by
the ID).
PHP offers several data functions which may or may not meet your needs but
the important thing to remember is that next year the dayID must not go back
to 1 - rather tomorrow will always be dayID++.
So if your table looked like:
Dog
-----
name varchar(20),
mom varchar(20),
dad varchar(20),
birthDayID int,
sex char
You could do a query like:
Select mom, count(*) from Dog
where birthDayID in
(select dayID from DateTable where year = 2000)
Group by mom
Which would show you all the mothers who had given birth in 2000 and the
number of children for each. Now notice how easy it is to change the "where
year = 2000" to where quarter = 2 and year = 2001 and so on...
> I need to generate some statistics to work out average numbers of pairs
> per month, averaged on a daily basis, for a given start and stop date,
> typically a year or year-to-date.
Select month, count(*) from Dog, DateTable
where birthDayID = myDate
and myDate >= "1/1/2000" /*start date */
and myDate < "6/1/2000" /* end date */
Group by month
-- this is the total per month for the first 5 months of 2000, you can
figure the average from here. Change the month to quarter, dayOfYear or
year or even myDate for daily totals and you're off.
This set up works best where the number of records per day is high - you
only need one int field in your data table (sometimes called 'fact table')
and you can be as anal as you like with your DateTable - even go back and
add more attributes later without messing with your fact table.
The advantage is you do the "work" on the DateTable and then it's a simple
join to your fact table.
Good Luck,
Frank [EMAIL PROTECTED]
On 1/29/02 9:21 AM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
>
> From: Garry Optland <[EMAIL PROTECTED]>
> Date: Tue, 29 Jan 2002 19:10:25 +1100 (EST)
> To: <[EMAIL PROTECTED]>
> Subject: Date operations.
>
> Hi All,
>
> I have a problem working out a suitable algorithm either in PHP or MySQL.
>
> Basically I have a DB that keeps track of breeding records. Each record
> has a paired data, and a split-up date.
>
> I need to generate some statistics to work out average numbers of pairs
> per month, averaged on a daily basis, for a given start and stop date,
> typically a year or year-to-date.
>
> All the algorithms I can think of are messy, where I have to loop through
> all the breeding records for every day of the year, and count how many
> pairs are breeding by seeing if the date is between the start and stop
> dates, and then average that on a monthly basis. I can't see
> that scaling very well, as there might be several hundred breeding records
> for a given year, multiplied by 365 days.
>
> Has anyone any hints/pointers for an efficient way to do this?
>
> Regards,
> Garry.
>
--
Frank Flynn
Poet, Artist & Mystic
--
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]
