title: MySQL Date Between Subquery Question
body: <p>Ok, I have a subquery that I am trying to work with and was needing a complicated question answered. There is a SQL tool that is being used to generate reports. With this tool, you can select dates as long as the date is in the outer select statement. So, you can use this tool to select a date range for instance from a simple select statement like this:</p>
  
  <pre><code>select date_entered, name from opportunities
  </code></pre>
  
  <p>This will work fine with the tool.</p>
  
  <p>However, I have a SQL requirement where I need to have each column be it's own SQL statement (basically, a series of subqueries). My question is - is there anyway to have a subquery use the date from the initial select? Here is a sample of a select statement that I am having to do:</p>
  
  <pre><code>SELECT
  (
      SELECT
          count(
              opportunities.id) AS "count_for_opp_id"
  
      FROM
          opportunities 
  LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
  WHERE
      opportunities.deleted = '0'
  AND ((curdate() - interval 0 day) &lt;= opportunities.date_entered)
  AND opportunities_cstm.lead_category_c = 'GM Internet'
  AND opportunities_cstm.lead_type_c = 'Sales'
  AND opportunities_cstm.partner_id_c LIKE '41%'
  OR opportunities_cstm.partner_id_c LIKE '42%'
  OR opportunities_cstm.partner_id_c LIKE '43%'
  OR opportunities_cstm.partner_id_c LIKE '44%'
  OR opportunities_cstm.partner_id_c LIKE '45%'
  OR opportunities_cstm.partner_id_c LIKE '47%'
  OR opportunities_cstm.partner_id_c LIKE '48%'
  OR opportunities_cstm.partner_id_c LIKE '49%'
  OR opportunities_cstm.lead_sub_type_c = 'GM 3rd Party'
  )AS TOT_DIV_THIRD_PARTY_SALES_LEADS,
  (
  SELECT
  count(opportunities.id) AS "count_for_opp_id"
  FROM
  opportunities left
      JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
  WHERE
      opportunities.deleted = '0'
  AND ((curdate() - interval 0 day) &lt;= opportunities.date_entered)
  AND opportunities_cstm.lead_category_c = 'Dealer Web'
  AND opportunities_cstm.lead_type_c = 'Sales'
  AND opportunities_cstm.lead_sub_type_c = 'GM Dealer Website'
  OR opportunities_cstm.lead_sub_type_c = 'DMA Landing Page'
  )AS TOT_DEALER_WEBSITE_DMA_SALES_LEADS,
  (
      SELECT
          count(opportunities.id) AS "count_for_opp_id"
      FROM
          opportunities left
      JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
  JOIN leads_handling_objectives
  WHERE
  opportunities.deleted = '0'
  AND ((curdate() - interval 0 day) &lt;= opportunities.date_entered)
  AND opportunities_cstm.lead_category_c = 'GM Internet'
  AND opportunities_cstm.partner_id_c LIKE '41%'
  OR opportunities_cstm.partner_id_c LIKE '42%'
  OR opportunities_cstm.partner_id_c LIKE '43%'
  OR opportunities_cstm.partner_id_c LIKE '44%'
  OR opportunities_cstm.partner_id_c LIKE '45%'
  OR opportunities_cstm.partner_id_c LIKE '47%'
  OR opportunities_cstm.partner_id_c LIKE '48%'
  OR opportunities_cstm.partner_id_c LIKE '49%'
  OR opportunities_cstm.lead_sub_type_c = 'GM 3rd Party'
  AND opportunities_cstm.time_followup_c &lt;= leads_handling_objectives.resp_time_obj
  AND opportunities_cstm.make_c = leads_handling_objectives.makes_carried
  AND opportunities_cstm.time_followup_c IS NOT NULL
  )AS NUM_OF_LEADS_RESP_ONTIME_DIVISIONAL_THIRD_PARTY,
  (
      SELECT
          count(opportunities.id) AS "count_for_opp_id"
      FROM
          opportunities left
      JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
  JOIN leads_handling_objectives
  WHERE
  opportunities.deleted = '0'
  AND ((curdate() - interval 0 day) &lt;= opportunities.date_entered)
  AND opportunities_cstm.lead_category_c = 'Dealer Web'
  AND opportunities_cstm.lead_sub_type_c = 'GM Dealer Website'
  OR opportunities_cstm.lead_sub_type_c = 'DMA Landing Page'
  AND opportunities_cstm.time_followup_c &lt;= leads_handling_objectives.resp_time_obj
  AND opportunities_cstm.make_c = leads_handling_objectives.makes_carried
  AND opportunities_cstm.time_followup_c IS NOT NULL
  )AS NUM_OF_LEADS_RESP_ONTIME_DEALERWEBSITE_DMA
  </code></pre>
  
  <p>If you look at this query, you will notice the curdate...&lt;= date_entered part of the subquery. I need to really have this on the outer layer somehow of the select statement. However, if I put this on the outer layer, I am not sure how to make the subquery use the date range that way.</p>
  
  <p>I know this is a complicated question but - would there be any advice at all from anyone out there? Thanks!</p>
  
  <p>(note: Also, this cannot be a procedure or a function. It has to be a select statement or a view to work with the reporting tool.)</p>
  
  <p>One more edit - an easier (smaller) snippet of the SQL above can be focused on here:</p>
  
  <pre><code>SELECT
  (SELECT
          count(
              opportunities.id) AS "count_for_opp_id"
  
      FROM
          opportunities 
  LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
  WHERE
      opportunities.deleted = '0'
  AND ((curdate() - interval 0 day) &lt;= opportunities.date_entered)
  AND opportunities_cstm.lead_category_c = 'GM Internet'
  AND opportunities_cstm.lead_type_c = 'Sales'
  ) AS TOT_DIV_THIRD_PARTY_SALES_LEADS
  </code></pre>
  
  <p>This one above might be easier to read and easier to diagnose in the long run.</p>
  
  <p>Thanks!</p>