Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Nov 2013
    Posts
    1
    Thanks
    0
    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
    WHERE
    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?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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 **

    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •