MySQL query - Passing variable into subquery's derived table

Question

Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery?

I am trying identify "active" organizations, and there are multiple actions that constitute "activity". One is creating an invoice, represented here (others not shown here for the sake of readability). However, because there are more types of actions, I cannot do the simpler approach of just querying the Invoice table, like in thisWorks column.

Therefore, I am trying to create a derived table, where multiple sources can be queried, but also filtering the @start and @end timestamps, as seen in the thisDoesntWork column.

Here is a simplified version of what the query looks like:

select
    @row:=@row+1 as row,
    @sixPrior:=date_sub(date_sub(date_sub(curdate(), interval dayofweek(curdate())-2 day), interval @row-1 week), interval 6 month) as sixMonthsPrior,
    @start:=date_sub(date_sub(curdate(), interval dayofweek(curdate())-2 day), interval @row-1 week) as start,
    @end:=date_sub(date_add(curdate(), interval dayofweek(curdate())+5 day), interval @row-1 week) as end,
    @sixPrior as sixCheck,
    @start as startCheck,
    @end as endCheck,
    ( select count(distinct(organizationId))
      from Invoice
      where createdTime between @start and @end
    ) as thisWorks,
    (
        select count(distinct(organizationId))
        from (
            select organizationId
            from Invoice
            where createdTime between @start and @end
        ) d
    ) as thisDoesntWork
from Organization o
join (select @row:=0) r
limit 10;

In the example above, thisDoesntWork, has the same value for every row (this first value). I assume it's got something to do with @start and @end never getting updated values.

Is there any way to filter a derived table, with values that change each row, from an outer table?

I'm also open to alternative approaches, if derived table restrictions will never allow it to work.

I'm also using MySql 5.7, so LATERAL JOIN is not a possibility

Answer 1

Use GROUP BY

And transform the "week" into a number that can be used in the GROUP BY: FLOOR((TO_DAYS(createdTime) - 1)/7). As a simple test:

SELECT FLOOR((TO_DAYS(CURDATE()) - 1)/7);
+-----------------------------------+
| FLOOR((TO_DAYS(CURDATE()) - 1)/7) |
+-----------------------------------+
|                            105446 |
+-----------------------------------+

That FLOOR expression gives you a "week number" that for use with GROUP BY. (Tweek the "-1" if I don't have the week starting on the right day of week.) You can derive "start" and "end" from that number via some simple expressions like

SELECT FROM_DAYS(105446 * 7) AS 'start',
       FROM_DAYS(105446 * 7 + 6) AS 'end';
+------------+------------+
| start      | end        |
+------------+------------+
| 2020-11-28 | 2020-12-04 |
+------------+------------+