473,573 Members | 3,248 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

subquery workaround for v3.23.56

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(dat e,'%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.
Jul 20 '05 #1
4 1697
> 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_mon th (MONTH_NO integer, DAY_NO integer);
INSERT INTO last_day_of_mon th 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_mon th AS L
ON (MONTH(U.`date` ) = L.MONTH_NO AND DAYOFMONTH(U.`d ate`) =
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.`dat e`);

Regards,
Bill K.
Jul 20 '05 #2
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.
Jul 20 '05 #3
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.co m> wrote in message news:<cl******* **@enews1.newsg uy.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_mon th (MONTH_NO integer, DAY_NO integer);
INSERT INTO last_day_of_mon th 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_mon th AS L
ON (MONTH(U.`date` ) = L.MONTH_NO AND DAYOFMONTH(U.`d ate`) =
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.`dat e`);

Regards,
Bill K.

Jul 20 '05 #4
ab***@spammersa regay.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_la st_query )

Regards,
Bill K.
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
9195
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correlated or not)
0
2337
by: Steve Claflin | last post by:
I'm using mysql 4.0.x, which does not allow the following query (which works fine in 4.1). Is there any way I can issue a single query to achieve the same results? (I want a list of all records from table al, nulled where there is no match in table alm, which has been filtered. Without a subquery, the filtering occurs after the outer join,...
0
563
by: Greg Stark | last post by:
Postgresql 7.4b2 (approximately, compiled out of CVS) When I have a subquery that has a complex subquery as one of the result columns, and then that result column is used multiple times in the parent query, the subquery is inlined for each one. This means multiple redundant executions of the subquery. I recall there was a way to defeat...
8
19578
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two...
1
7189
by: Tim Marshall | last post by:
A2003. I am getting this error message when I try to set a report's recordsource to an SQL statement or a saved querydef that uses sub-queries. I've debug.printed the SQL, and run it as a stand alone query, as a rowsource for a list box and as a recordsource for a form. There's no problem. I'm only encountering this in reports. As a...
4
6233
by: Diogenes | last post by:
Kind readers, I am currently developing an app with PHP 5 & MySql 5. The following select statement works as expected from the MySql command line but produces an error when run from PHP. SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones" AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
1
4191
by: Dan Amodeo | last post by:
I am using DB2 (version 7, soon to be updated to 8) on a mainframe that runs Z/OS. It seems to me that the following query is legitimate, but I get an error message. (This is not a real query; I'm just using it to experiment.) SELECT CLASS, TYPE FROM J3 WHERE TYPE > COALESCE ( (SELECT X FROM J1
1
7150
by: Franc Walter | last post by:
Hello, i didn't find it in the help, i think it is not possible, but i try to question anyway: Is the MySQL command "LIMIT" possible with a subquery in MySQL 4.0.27? e.g.: SELECT * FROM tab1 LIMIT (SELECT COUNT(*) from tab2) this doesn't work. MySQL 5.1 writes: User variables may be used in contexts where expressions are allowed.
5
8018
by: Anne | last post by:
Hello! Here is the statement in question: --STATEMENT A SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTable) The problem with Statement A is that 'colX' does not exist in 'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'. Breaking the statement down, we have:
0
7779
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8021
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7781
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5291
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3732
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3732
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2208
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1301
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1040
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.