subquery workaround for v3.23.56

[abuse]

i want do the following but am limited by my version of mysql which I
am not able to upgrade. please advise.

uptime_table
************
date
num_days_up
....
************

I want select to get the value of num_days_up from the last recorded
day of each month for the current year. The current year is passed as
a variable. So the result should be 12 rows.

So far I've found I can group by month by using group by
FORMAT_DATE(date,'%m'). I also found that: "select num_days_up,
max(date) ..." returns the correct date but not the corresponding
num_days_up. I would like to use a subquery but apparently 3.23
doesnt have support for them.

[Bill Karwin]

> uptime_table

************
date
num_days_up
...
************

I want select to get the value of num_days_up from the last recorded
day of each month for the current year. The current year is passed as
a variable. So the result should be 12 rows.

Something like the following solution should do it:

CREATE TABLE last_day_of_month (MONTH_NO integer, DAY_NO integer);
INSERT INTO last_day_of_month VALUES
(1, 31), (2, 28), (3, 31), (4, 30), (5, 31), (6, 30),
(7, 31), (8, 31), (9, 30), (10, 31), (11, 30), (12, 31);

SELECT U.num_days_up
FROM uptime_table AS U INNER JOIN last_day_of_month AS L
ON (MONTH(U.`date`) = L.MONTH_NO AND DAYOFMONTH(U.`date`) =
IF(
MONTH(U.`date`)=2
AND YEAR(U.`date`)%4=0
AND NOT YEAR(U.`date`)%100=0
OR YEAR(U.`date`)%400=0,
L.DAY_NO+1, L.DAY_NO)
);

I've tested this technique against a database containing dates that I
have, and it seems to work correctly for leap years and non-leap years.

Someday if you can upgrade to MySQL 4.1.1, you should use the LAST_DAY()
function to simplify the query:

SELECT U.num_days_up
FROM uptime_table AS U
WHERE DATE(U.`date`) = LAST_DAY(U.`date`);

Regards,
Bill K.

[Bill Karwin]

Bill Karwin wrote:

IF(
MONTH(U.`date`)=2
AND YEAR(U.`date`)%4=0
AND NOT YEAR(U.`date`)%100=0
OR YEAR(U.`date`)%400=0,
L.DAY_NO+1, L.DAY_NO)
);

Oops! Very sorry to have to repost, but this should be:
IF(
MONTH(U.`date`)=2
AND (YEAR(U.`date`)%4=0
AND NOT YEAR(U.`date`)%100=0
OR YEAR(U.`date`)%400=0),
L.DAY_NO+1, L.DAY_NO)
);

I was being too stingy with parentheses!

Bill K.

[abuse]

Thanks Bill, I appreciate your response.

One problem however. I cannot assume the last recorded day for each
month *is* the last day of the month. Since this is an uptime table,
if the server is down for a day then it wouldn't exist. More
importantly (but I didn't think of it this way before), the query
could be run at any day in the month and would need to be able to
report the correct uptime.

For example, I could run the query today, the 27th of october which is
the latest day that the uptime is record for in this month but not the
last day of the month.

The best way to find the latest date recorded that I can think of is
max(date) and group by month-year...but I can't make it work. Maybe I
should remove the uptime column and write a second program to populate
another table which updates the same field for each month (12 tuples).
I was just hoping to avoid that.

-Kevin

Bill Karwin <bi**@karwin.com> wrote in message news:<cl*********@enews1.newsguy.com>...

uptime_table
************
date
num_days_up
...
************

I want select to get the value of num_days_up from the last recorded
day of each month for the current year. The current year is passed as
a variable. So the result should be 12 rows.

Something like the following solution should do it:

CREATE TABLE last_day_of_month (MONTH_NO integer, DAY_NO integer);
INSERT INTO last_day_of_month VALUES
(1, 31), (2, 28), (3, 31), (4, 30), (5, 31), (6, 30),
(7, 31), (8, 31), (9, 30), (10, 31), (11, 30), (12, 31);

SELECT U.num_days_up
FROM uptime_table AS U INNER JOIN last_day_of_month AS L
ON (MONTH(U.`date`) = L.MONTH_NO AND DAYOFMONTH(U.`date`) =
IF(
MONTH(U.`date`)=2
AND YEAR(U.`date`)%4=0
AND NOT YEAR(U.`date`)%100=0
OR YEAR(U.`date`)%400=0,
L.DAY_NO+1, L.DAY_NO)
);

I've tested this technique against a database containing dates that I
have, and it seems to work correctly for leap years and non-leap years.

Someday if you can upgrade to MySQL 4.1.1, you should use the LAST_DAY()
function to simplify the query:

SELECT U.num_days_up
FROM uptime_table AS U
WHERE DATE(U.`date`) = LAST_DAY(U.`date`);

Regards,
Bill K.

[Bill Karwin]

ab***@spammersaregay.com wrote:

Thanks Bill, I appreciate your response.

One problem however. I cannot assume the last recorded day for each
month *is* the last day of the month. Since this is an uptime table,
if the server is down for a day then it wouldn't exist. More
importantly (but I didn't think of it this way before), the query
could be run at any day in the month and would need to be able to
report the correct uptime.

For example, I could run the query today, the 27th of october which is
the latest day that the uptime is record for in this month but not the
last day of the month.

My apologies, I misunderstood the problem.
I tried this:

SELECT MAX(`date`), num_days_up
FROM update_table
GROUP BY YEAR(`date`), MONTH(`date`)

But the value of num_days_up then comes from the *first* record in the
defined group, not the last record in the group. That's clearly not
what you want.

If the num_days_up always increases during a given month, then you could
use this:

SELECT MAX(`date`), MAX(num_days_up)
FROM update_table
GROUP BY YEAR(`date`), MONTH(`date`)

But you might have to resort to doing this in two queries:

SELECT MAX(`date`)
FROM update_table
GROUP BY YEAR(`date`), MONTH(`date`)

SELECT num_days_up
FROM update_table
WHERE `date` IN ( @result_from_last_query )

Regards,
Bill K.