query problem

[Danny Kjærgaard]

Hi.

I have a table containing:

id, date, open, close, high, low, volumen, share_id

The table is used for daily stock information. I want to make a query that
count rows where open, close, high,low, volumen, share_id are the same for
the last 2 days that are in the date field.

example:

id | date | open| close | high | low | volumen | share_id
1 | 2005-03-22 | 61.5 | 61 | 61.5 | 61 | 12369 | 1
283 | 2005-03-23 | 61 | 61 | 61.5 | 61 | 29141 | 1
565 | 2005-03-24 | 61 | 61 | 61.5 | 61 | 29141 | 1
In this case i want a count so i know that there are 1 (or more) rows that
are identical with the day before.

This will be used to determine if the last day was a nonstockday. If 99% of
the rows with the highest date is identical with the secound highest date it
is properly a nonstockday.

I run mysql 4.0.23 so i cant use the subquery. I really dont have any idea
how to solve this. I cant even get a query to return the secound highest
date to work. I tried:

SELECT max(date) from daily_rate_of_exchange_1
where date <= DATE_ADD(max(date),interval -1 day);

but get : Invalid use of group function.

If i insert a date instead of the max(date) in the DATE_ADD function it
works. But i need it to be dynamic.

As you can tell im new to mysql queries, but would be thankfull if someone
would post an idea, or point in a direction where the anwser might be.

Thanks

-D Kjærgaard

[Felix Geerinckx]

On 29/03/2005, Danny Kjærgaard wrote:

The table is used for daily stock information. I want to make a query
that count rows where open, close, high,low, volumen, share_id are
the same for the last 2 days that are in the date field.
[...]
In this case i want a count so i know that there are 1 (or more) rows
that are identical with the day before.

This will be used to determine if the last day was a nonstockday. If
99% of the rows with the highest date is identical with the secound
highest date it is properly a nonstockday.

With

mysql> select * from stockinfo;
+----+------------+------+-------+--------+
| id | d | open | close | volume |
+----+------------+------+-------+--------+
| 1 | 2005-03-25 | 10 | 11 | 1000 |
| 2 | 2005-03-26 | 10 | 11 | 1000 |
| 3 | 2005-03-27 | 10 | 11 | 1000 |
| 4 | 2005-03-28 | 12 | 14 | 1100 |
+----+------------+------+-------+--------+
4 rows in set (0.01 sec)

the query

mysql> SELECT
-> s1.d AS DATE,
-> IF(s1.open = s2.open AND
-> s1.close = s2.close AND
-> s1.volume = s2.volume, 'NO', 'YES') AS STOCKDAY
-> FROM stockinfo AS s1
-> LEFT JOIN stockinfo AS s2 ON DATEDIFF(s1.d, s2.d) = 1;

gives the following result:

+------------+----------+
| DATE | STOCKDAY |
+------------+----------+
| 2005-03-25 | YES |
| 2005-03-26 | NO |
| 2005-03-27 | NO |
| 2005-03-28 | YES |
+------------+----------+
4 rows in set (0.00 sec)

Is this what you want?
--
felix

[Danny Kjærgaard]

Well.

I dont work over a weekend. With the DATEDIFF =1. And i would like i to
return a count (like 700 out of 750 returned as a nonstockday, thats 93%
that were like the last stockday and it is likely a nonstockday then).

But its nice and i got some input for a way to work this out.

Thanks.

"Felix Geerinckx" <fe*************@gmail.com> wrote in message
news:xn***************@news.easynews.com...

On 29/03/2005, Danny Kjærgaard wrote:

The table is used for daily stock information. I want to make a query
that count rows where open, close, high,low, volumen, share_id are
the same for the last 2 days that are in the date field.
[...]
In this case i want a count so i know that there are 1 (or more) rows
that are identical with the day before.

This will be used to determine if the last day was a nonstockday. If
99% of the rows with the highest date is identical with the secound
highest date it is properly a nonstockday.

With

mysql> select * from stockinfo;
+----+------------+------+-------+--------+
| id | d | open | close | volume |
+----+------------+------+-------+--------+
| 1 | 2005-03-25 | 10 | 11 | 1000 |
| 2 | 2005-03-26 | 10 | 11 | 1000 |
| 3 | 2005-03-27 | 10 | 11 | 1000 |
| 4 | 2005-03-28 | 12 | 14 | 1100 |
+----+------------+------+-------+--------+
4 rows in set (0.01 sec)

the query

mysql> SELECT
-> s1.d AS DATE,
-> IF(s1.open = s2.open AND
-> s1.close = s2.close AND
-> s1.volume = s2.volume, 'NO', 'YES') AS STOCKDAY
-> FROM stockinfo AS s1
-> LEFT JOIN stockinfo AS s2 ON DATEDIFF(s1.d, s2.d) = 1;

gives the following result:

+------------+----------+
| DATE | STOCKDAY |
+------------+----------+
| 2005-03-25 | YES |
| 2005-03-26 | NO |
| 2005-03-27 | NO |
| 2005-03-28 | YES |
+------------+----------+
4 rows in set (0.00 sec)

Is this what you want?
--
felix

[Danny Kjærgaard]

DATEDIFF() was added in MySQL 4.1.1.

So i dont even work on my version.

"Danny Kjærgaard" <tu****@GeilerHiFi.dk> wrote in message
news:42***********************@dreader2.cybercity. dk...

Well.

I dont work over a weekend. With the DATEDIFF =1. And i would like i to
return a count (like 700 out of 750 returned as a nonstockday, thats 93%
that were like the last stockday and it is likely a nonstockday then).

But its nice and i got some input for a way to work this out.

Thanks.

"Felix Geerinckx" <fe*************@gmail.com> wrote in message
news:xn***************@news.easynews.com...

On 29/03/2005, Danny Kjærgaard wrote:

The table is used for daily stock information. I want to make a query
that count rows where open, close, high,low, volumen, share_id are
the same for the last 2 days that are in the date field.
[...]
In this case i want a count so i know that there are 1 (or more) rows
that are identical with the day before.

This will be used to determine if the last day was a nonstockday. If
99% of the rows with the highest date is identical with the secound
highest date it is properly a nonstockday.

With

mysql> select * from stockinfo;
+----+------------+------+-------+--------+
| id | d | open | close | volume |
+----+------------+------+-------+--------+
| 1 | 2005-03-25 | 10 | 11 | 1000 |
| 2 | 2005-03-26 | 10 | 11 | 1000 |
| 3 | 2005-03-27 | 10 | 11 | 1000 |
| 4 | 2005-03-28 | 12 | 14 | 1100 |
+----+------------+------+-------+--------+
4 rows in set (0.01 sec)

the query

mysql> SELECT
-> s1.d AS DATE,
-> IF(s1.open = s2.open AND
-> s1.close = s2.close AND
-> s1.volume = s2.volume, 'NO', 'YES') AS STOCKDAY
-> FROM stockinfo AS s1
-> LEFT JOIN stockinfo AS s2 ON DATEDIFF(s1.d, s2.d) = 1;

gives the following result:

+------------+----------+
| DATE | STOCKDAY |
+------------+----------+
| 2005-03-25 | YES |
| 2005-03-26 | NO |
| 2005-03-27 | NO |
| 2005-03-28 | YES |
+------------+----------+
4 rows in set (0.00 sec)

Is this what you want?
--
felix

[Danny Kjærgaard]

I just upgraded the sql server to 4.1.10 so i can do subqueries and the
datediff now works. Just an information if someone feels like posting a way.

Thanks.

-Danny

"Danny Kjærgaard" <tu****@GeilerHiFi.dk> wrote in message
news:42***********************@dreader2.cybercity. dk...

DATEDIFF() was added in MySQL 4.1.1.

So i dont even work on my version.

"Danny Kjærgaard" <tu****@GeilerHiFi.dk> wrote in message
news:42***********************@dreader2.cybercity. dk...

Well.

I dont work over a weekend. With the DATEDIFF =1. And i would like i to
return a count (like 700 out of 750 returned as a nonstockday, thats 93%
that were like the last stockday and it is likely a nonstockday then).

But its nice and i got some input for a way to work this out.

Thanks.

"Felix Geerinckx" <fe*************@gmail.com> wrote in message
news:xn***************@news.easynews.com...

On 29/03/2005, Danny Kjærgaard wrote:

> The table is used for daily stock information. I want to make a query > that count rows where open, close, high,low, volumen, share_id are
> the same for the last 2 days that are in the date field.
> [...]
> In this case i want a count so i know that there are 1 (or more) rows > that are identical with the day before.
>
> This will be used to determine if the last day was a nonstockday. If
> 99% of the rows with the highest date is identical with the secound
> highest date it is properly a nonstockday.

With

mysql> select * from stockinfo;
+----+------------+------+-------+--------+
| id | d | open | close | volume |
+----+------------+------+-------+--------+
| 1 | 2005-03-25 | 10 | 11 | 1000 |
| 2 | 2005-03-26 | 10 | 11 | 1000 |
| 3 | 2005-03-27 | 10 | 11 | 1000 |
| 4 | 2005-03-28 | 12 | 14 | 1100 |
+----+------------+------+-------+--------+
4 rows in set (0.01 sec)

the query

mysql> SELECT
-> s1.d AS DATE,
-> IF(s1.open = s2.open AND
-> s1.close = s2.close AND
-> s1.volume = s2.volume, 'NO', 'YES') AS STOCKDAY
-> FROM stockinfo AS s1
-> LEFT JOIN stockinfo AS s2 ON DATEDIFF(s1.d, s2.d) = 1;

gives the following result:

+------------+----------+
| DATE | STOCKDAY |
+------------+----------+
| 2005-03-25 | YES |
| 2005-03-26 | NO |
| 2005-03-27 | NO |
| 2005-03-28 | YES |
+------------+----------+
4 rows in set (0.00 sec)

Is this what you want?
--
felix

[Felix Geerinckx]

On 29/03/2005, Danny Kjærgaard wrote:

I dont work over a weekend. With the DATEDIFF =1.
I don't understand what this has to do with the solution.
And i would like i to return a count (like 700 out of 750 returned as
a nonstockday, thats 93% that were like the last stockday and it is
likely a nonstockday then).

Then use the following query (I assume at most 1 entry per stock per
day):

SELECT
s1.d AS DATE,
SUM(IF(s1.open = s2.open AND
s1.close = s2.close AND
s1.volume = s2.volume, 1, 0)) /
(SELECT COUNT(id) FROM stockinfo WHERE d = s1.d) AS COUNT
FROM stockinfo AS s1
LEFT JOIN stockinfo AS s2 ON DATEDIFF(s1.d, s2.d) = 1
GROUP BY DATE

If the number of stocks is fixed, you can use this fixed number instead
of the SELECT COUNT(id) ...

If you don't have DATEDIFF, you can use TO_DAYS(date1) - TO_DAYS(date2)

--
felix