Query inside query on google spreadsheet

1
votes

I need to query data from table and for where clause use result from another query

For example:

How to query for rows at this tab where date on column A is greater or equal date from another query on the tab shown at second image below. I want to write only the project number on the query or put a cell with the current project number and query for start and end dates where project is equal current project.

Project Tasks

Project Dates

The query will be something like this:

Example Query

Can you help me?

1
google-sheets

1 Answers

1
votes

Sure it's possible if you use appropriate syntax.

I suggest query + filter combination:

Sheet1

Date    Col1    Col2
3/29/2017   a   a2
3/30/2017   b   b3
3/31/2017   c   c4
4/1/2017    d   d5
4/2/2017    e   e6
4/3/2017    f   f7
4/4/2017    g   g8
4/5/2017    h   h9
4/6/2017    i   i10
4/7/2017    j   j11
4/8/2017    k   k12

Sheet2

Project   Date1         Date2
1         3/29/2017     4/1/2017
2         4/2/2017      4/5/2017
3         4/6/2017      4/8/2017

Sheet3: Resulting formula

The formula is:

=QUERY({Sheet1!A:C},"select * where Col1 >= date '"&TEXT(FILTER(Sheet2!B:B,Sheet2!A:A=1),"YYYY-MM-DD")&"' and Col1 <= date '"&TEXT(FILTER(Sheet2!C:C,Sheet2!A:A=1),"YYYY-MM-DD")&"'")

The result:

Date    Col1    Col2
3/29/2017   a   a2
3/30/2017   b   b3
3/31/2017   c   c4
4/1/2017    d   d5

It's better to use filter once:

=FILTER(Sheet2!B:C,Sheet2!A:A=1)

to get dates for project #1 and then reference it's result.

Or even convert it to date format:

=FILTER(text(Sheet2!B:C,"\dat\e 'YYYY-MM-DD'"),Sheet2!A:A=1)

and get 2 strings:

date '2017-03-29'   date '2017-04-01'

I've also tried to use shorter query: select * where Col1 between date '2017-03-29' and date '2017-04-01' but between keyword is not supported.