mysql - How to find Top Losers for 1 day , 1 Month based on Historical data

Question

I have the following table structure for storing the historical data .

  CREATE TABLE `historical_data` (
      `symbol_name` varchar(70) DEFAULT NULL,
      `current_day` varchar(50) DEFAULT NULL,
      `open_val` varchar(50) ,
      `low_val` varchar(50) ,
     `close_val` varchar(50) ,
     `high_val` varchar(50) ,
      `prevclose_val` varchar(50) 
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

And this is my sample data

From the current date i am trying to find out Top Losers for 1 week and 1 month from the current date

I have tried as following

select symbol_name, (prevclose_val-close_val) as losers  from historical_data 
order by losers asc limit 10

Could you please tell me how can i make this work for 1 week and 1 month .

The sample data wil be in this format

 Insert Into historical_data values('SBIN','14-DEC-2015','300.10','289.00','305.10','309.90','256.30');

sql fiddle

http://sqlfiddle.com/#!9/47794

Answer 1

You could do something like this*:

select * from (select symbol_name , (prevclose_val-close_val) as losers,'daily' as `type` from historical_data where current_day >= '2015-12-14' order by losers asc limit 10 ) as sub_daily
union
select * from (select symbol_name , (prevclose_val-close_val) as losers,'weekly' as `type` from historical_data where current_day >= '2015-12-7' order by losers asc limit 10 ) as sub_weekly
union
select * from (select symbol_name , (prevclose_val-close_val) as losers,'monthly' as `type` from historical_data where current_day >= '2015-11-14' order by losers asc limit 10 ) as sub_monthly;

By the way, you should really change your date field from "varchar" into "DATE" or "DATETIME" and use yyyy-mm-dd format for your dates. That way you can use MySQL to do calculations on your date.

* this query requires your current_day field to be a date or datetime type.