A CROSS JOIN returns the cartesian product of two tables. On my answer to Show all combinations for a selection of columns I explain how to do a cartesian product by using built-in functions.
Once we have the CROSS JOIN / CARTESIAN PRODUCT we could use QUERY to filter the array, but Google Query Language doesn't include the BETWEEN operator so we should use instead two conditions and the AND
operator
Formula using commas as argument separator
=QUERY(ArrayFormula(SPLIT(
SORT(TRANSPOSE(
split(REPT(JOIN(",",TRANSPOSE(ID_1))&",",(COUNT(ID_1)*COUNTA(ID_2))/COUNTA(ID_1)),",")),1,TRUE)
&","&
TRANSPOSE(
split(REPT(JOIN("|",ARRAYFORMULA(QUERY(TRANSPOSE(table2&","),,2000000)))&"|",(COUNT(ID_1)*counta(ID_2))/COUNTA(ID_2)),"|"))
,",")),"select Col1,Col2 where Col1 > Col3 and Col1 < Col4 order by Col1")
Formula using semicolons as argument separator
=QUERY(ArrayFormula(SPLIT(
SORT(TRANSPOSE(
split(REPT(JOIN(",";TRANSPOSE(ID_1))&",";(COUNT(ID_1)*COUNTA(ID_2))/COUNTA(ID_1));","));1;TRUE)
&","&
TRANSPOSE(
split(REPT(JOIN("|";ARRAYFORMULA(QUERY(TRANSPOSE(table2&",");;2000000)))&"|";(COUNT(ID_1)*counta(ID_2))/COUNTA(ID_2));"|"))
;","));"select Col1,Col2 where Col1 > Col3 and Col1 < Col4 order by Col1")
where
- ID_1 is the named range for the dates sent table
- table2 is the named range for the users table
- ID_2 is the named range for the users column from the users table
NOTES:
- Before copy/paste any of the above formulas, create the named ranges.