title: Filtering out good company result based on quarter date in MySQL
body: <p>I have a table <code>'QuarterlyReport'</code> which has multiple company's quarterly profit figures with the following column <code>'CompanyName'</code>, <code>'QuarterEndDate'</code> &amp; <code>'Profit'</code></p>
  
  <pre>
  |CompanyName|QuarterEndDate|Profit|
  |---------------------------------|
  |A          |2013-06-30    |29878 |
  |A          |2013-09-30    |33712 |
  |A          |2013-12-31    |60764 |
  |A          |2014-03-31    |260734|
  |B          |2013-06-30    |-1234 |
  |B          |2013-09-30    |0     |
  |B          |2013-12-31    |20114 |
  |B          |2014-03-31    |-984  |
  ...
  </pre>
  
  <p>I am trying to construct a MySQL query to see which company has a profit performance which increases in every consecutive quarters (Q4>Q3>Q2>Q1), with a 1 year (4 quarters) date range. </p>
  
  <p>In the case of example above, only Company 'A' will meet this requirement and shall be return as the query's result</p>
  
  <p>Currently I only able to construct query for (Q4>0 AND Q3>0 AND Q2>0) using follow MySQL query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM (SELECT q.CompanyName, q.QuarterEndDate, q.Profit FROM `QuarterlyReport` q) a
  WHERE a.QuarterEndDate &gt;= '2013-06-30' AND
  a.QuarterEndDate &lt; '2014-06-30' AND
  a.CompanyName IN (SELECT CompanyName FROM `QuarterlyReport` WHERE 
  a.CompanyName IN (SELECT Q4.CompanyName FROM `QuarterlyReport` AS Q4 WHERE Q4.QuarterEndDate = '2014-03-31' AND Q4.Profit &gt; '0') AND
  a.CompanyName IN (SELECT Q3.CompanyName FROM `QuarterlyReport` AS Q3 WHERE Q3.QuarterEndDate = '2013-12-31' AND Q3.Profit &gt; '0') AND
  a.CompanyName IN (SELECT Q2.CompanyName FROM `QuarterlyReport` AS Q2 WHERE Q2.QuarterEndDate = '2013-09-30' AND Q2.Profit &gt; '0') AND
  a.CompanyName IN (SELECT Q1.CompanyName FROM `QuarterlyReport` AS Q1 WHERE Q1.QuarterEndDate = '2013-06-30' AND Q1.Profit &gt; '0')
  GROUP BY a.CompanyName ORDER BY a.CompanyName ASC
  </code></pre>
  
  <p>Can anyone suggest some idea on how to archive my targeted query?</p>