Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
11-05-2013, 05:44 PM #1
- Join Date
- Nov 2013
- Thanked 0 Times in 0 Posts
Sql computation for different values from the same column in the same table at differ
I have to do a calculation based on the values in the column Price in between a particular
time slot say 9 to 10 a.m. In this table I am getting values such as 9:01, 9:03 ,9:10 upto 9:55 a.m. from which I have to consider the
values that are closer to 9:00 a.m i.e. 9:01 and 9:55 which is closer to 10:00 a.m.
I have implemented this in sql in stored procedure in the following way:
CREATE TEMPORARY TABLE IF NOT EXISTS table1 AS (SELECT DISTINCT
b1.Price AS current_price,b1.volume As volume, b1.dateTime,b1.Code
FROM mktvalues b1
DATE_FORMAT(b1.DateTime,'%H:%i:%s') <= '10:00:00'
AND DATE_FORMAT(b1.DateTime,'%H:%i:%s') >= '09:00:00'
GROUP BY b1.Code HAVING DATE_FORMAT(b1.DateTime,'%H:%i:%s') = MAX(DATE_FORMAT(b1.DateTime,'%H:%i:%s')));
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT DISTINCT
b2.Price AS prev_price,b2.volume As volume,b2.dateTime,b2.Code
FROM mktvalues b2
WHERE DATE_FORMAT(b2.DateTime,'%H:%i:%s') <= '10:00:00'
AND DATE_FORMAT(b2.DateTime,'%H:%i:%s') >= '09:00:00'
GROUP BY b2.Code
HAVING DATE_FORMAT(b2.DateTime,'%H:%i:%s') = MIN(DATE_FORMAT(b2.DateTime,'%H:%i:%s')));
Select *,(table1.current_price - table2.prev_price) as Difference from table1 join table2
on table1.Code = table2.Code
GROUP BY table1.Code
order BY Difference desc;
1) The first query will fetch value of price at maximum DateTime less than 10 a.m.
2) The second query will fetch value of price at minimum DateTime less than 10 a.m. but greater than 9:00 am
3) The third query will compute the difference of price values from the above queries based on the join condition
on Code column, the results will be grouped by Code and ordered by difference of price.
The problem that I am facing is that I am getting same values for Price column for 9 am and 10 am and
the difference is zero.How can I resolve this problem?
11-12-2013, 08:39 PM #2
STOP using strings to manipulate dates and times!!! The presence of DATE_FORMAT in a MySQL query is a strong indication that you have goofed in creating the query.
And I don't see any reason to be using temporary tables.
Here's half the query. Clone it for MIN(TIME(datetime)) to get prior_price and it should work.
** UNTESTED **
SELECT b1.Price AS current_price, b1.volume, b1.dateTime, b1.Code FROM mktvalues AS b1, ( SELECT code, MAX(TIME(datetime)) AS maxtime FROM mktvalues WHERE TIME(datetime) BETWEEN '9:00:00' AND '10:00:00' GROUP BY code ) AS s1 WHERE b1.code = s1.code AND TIME(b1.datetime) = s1.maxtime
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.