MySQL Date Between Subquery Question

Question

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:

select date_entered, name from opportunities

This will work fine with the tool.

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:

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) <= 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) <= 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) <= 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 <= 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) <= 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 <= 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

If you look at this query, you will notice the curdate...<= 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.

I know this is a complicated question but - would there be any advice at all from anyone out there? Thanks!

(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.)

One more edit - an easier (smaller) snippet of the SQL above can be focused on here:

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) <= 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

This one above might be easier to read and easier to diagnose in the long run.

Thanks!

Answer 1

Your (smaller) query as it stands lacks a FROM. Putting one in will give you a hook to hang the curdate() function from...

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)     <= 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
    FROM any_table
    LIMIT 1;

Second suggestion...

SELECT <given date>,
       (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
                  (<given date>                     <= 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
    FROM any_table
    LIMIT 1;

The given date can be a literal or a variable or it could be present in the any_table; it will work its way into the subquery just fine.