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
Therefore, I am trying to create a derived table, where multiple sources can be queried, but also filtering the
@end timestamps, as seen in the
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
@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