eBlush_Hector — Fri, 14 May 2004 01:13:11 +0000

Yes, Mark's idea is good. Also, to calculate the number of days between a given day and the end of the month in mySQL you can use what's in mySQL Cookbook in Recipe 5.22

SELECT d,
DAYOFMONTH(DATE_SUB(
DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY), INTERVAL 1 MONTH),
INTERVAL 1 DAY))
AS 'days in month'
FROM date_val;

Mark Hensler — Thu, 13 May 2004 23:53:56 +0000

Unless there is a way to calculate the number of days between April 23 and the end of the month. I don't know of a way off the top of my head (in sql).

Inserting a record for the begining (and maybe end) of the month may be the easiest solution.

Greg K — Thu, 13 May 2004 16:03:16 +0000

Mark, I like your idea, would save on the tons of "inserts" for each day they didn't save anything in my approach. The only trick still remaining is then finding it for just one month, and say they didn't do an update from 4-23 until 5-3.

Maybe when it has an update, just do an insert of a record for the begining of each month?

-Greg

Mark Hensler — Thu, 13 May 2004 00:15:32 +0000

I would advise use of a history table...

+----+--------+-------------+--------------+-----------------+
| id | client | change_date | change_value | change_duration |
+----+--------+-------------+--------------+-----------------+
|  1 |    123 | 2004-05-01  |         5000 |               4 |
|  2 |    123 | 2004-05-05  |         6500 |               7 |
|  3 |    123 | 2004-05-12  |         7000 |               0 |
+----+--------+-------------+--------------+-----------------+

'If change_duration is zero, then change_value is the currently effective value. When the user changes the value, run an update to calculate the duration for the last value and update the previous record. Then insert a new row with the new value and the days date.

Using PHP (or ASP), query the table for dates required, then perform your math:
change_value * change_duration / SUM(change_duration)

eBlush_Hector — Wed, 12 May 2004 18:20:46 +0000

It is much easier to do inserts and let the DB do the calcs, but I do not think the speed difference between your function and the DB calc will be too different. However, if you say the value may not change for weeks or months, then your function would prolly be more efficient, since you'll be taking shortcuts the DB cannot.

If development time is more precious right now, then let the DB do the calcs and create an optimized function later in the life of the project.... "hopefully if things go well"