Sub Query without Xpack or Nested mappings

[bsmith]

Trying to mimic the following SQL with the intent of using the results for Vega. SQL gets a count of employees who were active at the start date as well as the end date.

I have a feeling this can't be done without nested mappings, but I'm trying to avoid having to restructure the index around this.

select count(*)
from HEADCOUNT A
where DATE = '2021-01-02T00:00:00.000Z'
and STATUS = 'Active'
and EXISTS ( select 1
             from HEADCOUNT B
             where EMPLOYEE = B.EMPLOYEE
             and STATUS = 'Active'
             and DATE = '2021-03-31T00:00:00.000Z'

Below is an attempt at trying to put this together, but I know the headcount_sub is incorrect

"Headcount": {
          "filter": {
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "status": "active"
                    }
                  },
                  {
                    "match": {
                      "Time": "2021-01-02T00:00:00.000Z"
                    }
                  }
                ],
                "Headcount_SUB": {
                  "filter": {
                    "query": {
                      "bool": {
                        "must": [
                          {
                            "match": {
                              "status": "active"
                            }
                          },
                          {
                            "match": {
                              "Time": "2021-03-31T00:00:00.000Z"
                            }
                          }
                        ]
                      }
                    }
                  }
                },
                "minimum_should_match": 1
              }
            }
          },
          "aggs": {
            "headcount": {
              "value_count": {
                "field": "FIELD"
              }
            }
          }
        }