2 newbie queries

[jalea148]

There are 2 Mysql tables for Equities, Characteristics and Prices.
>>> Characteristics has 3 columns: Equity, AvgVol and Cap.
>>> Prices has 3 columns: Equity, Date and Price.
I would like to create 2 Views, each with 4 columns:
>>> Equity, Price with Date = Date1 as P1, Price with Date = Date2 as P2 and Gain = (P2 – P1)/P1.
View1 has the Top 50 ordered by Gain, Descending with Cap >= 2 and AvgVol > 100000
View2 has the Bottom 50 ordered by Gain, Ascending with Cap >= 2 and AvgVol > 100000
How should these Queries be written?
Thanx,
Jay

 

[r937]

where do Date1 and Date2 come from?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jalea148]

They are explicitly input as part of the query

 

[r937]

CREATE VIEW view1
AS
SELECT p.equity
     , p.p1 
     , p.p2
     , ( p.p2 - p.p1 ) / p.p1 AS gain
  FROM characteristics AS c
INNER
  JOIN ( SELECT equity
              , MAX(CASE WHEN `date` = '2012-09-09' -- date1
                         THEN price
                         ELSE NULL END ) AS p1 
              , MAX(CASE WHEN `date` = '2012-11-11' -- date2
                         THEN price
                         ELSE NULL END ) AS p2 
           FROM prices
          WHERE `date` IN ( '2012-09-09' , '2012-11-11' ) -- date1,date2
         GROUP
             BY equity ) AS p
    ON p.equity = c.equity 
 WHERE c.cap >= 2 
   AND c.avgvol > 100000    
ORDER
    BY gain DESC LIMIT 50

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jalea148]

I'm impressed; thanx,
Jay