# Aggregations by combining in/out datetimes to get inventory over time

**URL:** <https://discuss.elastic.co/t/aggregations-by-combining-in-out-datetimes-to-get-inventory-over-time/308484>\
**Category:** Elasticsearch\
**Created:** [June 29, 2022, 2:40pm UTC](https://discuss.elastic.co/t/aggregations-by-combining-in-out-datetimes-to-get-inventory-over-time/308484 "2022-06-29T14:40:33Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mattvl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattvl/32/83862_2.png) [@mattvl](https://discuss.elastic.co/u/mattvl)\
**Post date:** [June 29, 2022, 2:40pm UTC](https://discuss.elastic.co/t/aggregations-by-combining-in-out-datetimes-to-get-inventory-over-time/308484/1 "2022-06-29T14:40:33Z")

</div>

I have several inventory documents stored in elasticsearch, here is a simplified example (dates in database format `YYYY-MM-DD`):

```auto
[
    {"item_id": "a1", "in": "2020-12-01", "out": "2021-02-03"},
    {"item_id": "b2", "in": "2021-01-05", "out": "2021-03-05"},
    {"item_id": "c3", "in": "2021-02-06", "out": "2021-02-20"},
    {"item_id": "d4", "in": "2021-03-04", "out": null},
    {"item_id": "e5", "in": "2021-01-08", "out": "2021-05-03"},
    {"item_id": "f6", "in": "2021-05-10", "out": null},
    {"item_id": "g7", "in": "2021-05-04", "out": null},
    {"item_id": "h8", "in": "2021-02-06", "out": "2021-04-03"}
]

```

- The property `in` represents the date when the item was added to the inventory
- The property `out` represents the date when the item was removed from the inventory

I would like to calculate a histogram-like overview of the inventory items over time, grouped by month. The overview will contain the number of items satisfying the following condition for each month (logical `AND` between them):

- `in` should be less or equal to 1st day of the month
- `out` should be greater or equal to 1st day of the month or null

Here a visual representation of the dataset:

| item\_id | 2021-01 | 2021-02 | 2021-03 | 2021-04 | 2021-05 | 2021-06 |
| --- | --- | --- | --- | --- | --- | --- |
| **a1** | o | o | | | | |
| **b2** | | o | o | | | |
| **c3** | | | | | | |
| **d4** | | | | o | o | o |
| **e5** | | o | o | o | o | |
| **f6** | | | | | | o |
| **g7** | | | | | | o |
| **h8** | | | o | o | | |

What I would like to get in output is something that resembles this:

```auto
"buckets": [
    {
        "key": "2021-01",
        "doc_count": 1,
    },
    {
        "key": "2021-02",
        "doc_count": 3,
    },
    {
        "key": "2021-03",
        "doc_count": 3,
    },
    {
        "key": "2021-04",
        "doc_count": 4,
    },
    {
        "key": "2021-05",
        "doc_count": 2,
    },
    {
        "key": "2021-06",
        "doc_count": 3,
    }
]

```

Do you have any idea or advice on that? I don't think I can use the date histogram aggregation, or at least not without adding some conditions to it.

Thanks 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 27, 2022, 2:40pm UTC](https://discuss.elastic.co/t/aggregations-by-combining-in-out-datetimes-to-get-inventory-over-time/308484/2 "2022-07-27T14:40:47Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.