Query JSON with SQL filter using query result

Queries and Resources
1

I currently have a json sql query which returns totals for each month

select 
  mth_month as mth, 
  sum(earned) as earnedtot 
from 
  {{ formatDataAsArray(q_pay_hours.data) }}

I would like to filter this from a database table - this query brings me the value

select 
  mth_month as mth 
from 
  pay_months 
where 
  employer_id = {{ sel_employer.value }} and CURRENT_DATE between mth_start and mth_end

How do I filter the first query using the second?

2

Hey @Paul_Harvey! I believe the crux of the question is how to use an array of values in the where clause of the Query JSON w SQL query. Which is resolved here. But I did set up an example similar to yours :grinning:

Working with some test data in a Google sheet

rawSheetData
rawSheetData628×1128 50.3 KB

Write a Query JSON w SQL to return a subset of that data

filterByColumn1
filterByColumn1978×1708 87.8 KB

Use that data to filter the original data

finalFilterwWhere
finalFilterwWhere1728×1182 169 KB

The main functionality coming from the IN @(ARRAY) syntax. My data was coming through as an object so I had to do a map to return an array of Date strings.

Let me know if you have any issues setting that up!