HR use case: IntervalMatch & nested aggregations

did — Mon, 20 Dec 2021 21:38:40 GMT

Hi folks,

I have a typical HR use case with employee’ job contract times (employed from till) and missing times (from till).

Thanks to intervalmatch recommendations of Henric Cronström (HIC) it was possible to solve the 1^st main requirement (calculate and show only those employees which did not miss in selected month).

Now the 2^nd main requirement is: taking care of weekends and public holidays to calculate how many days per month each employee

must work
actually worked
actually missed.

What I managed so far:

How many days per month an employee must work
- Solution: all NetWorkDays of selected month – Public Holidays
If employee did not miss in selected month (i.e. he/she missed somewhere in the past or never missed at all)
- Solution: all NetWorkDays of selected month – Public Holidays
- Example_A (please see attached pdf)
  - in this case missing days are null, which is fine

Currently my difficulty is:

If employee has only 1 missing period in selected month
- Example_B (please see attached pdf)
  - in this case missing days = 3 (because 4.01.2020 was Saturday).
  - Works fine if
    - ONLY one row with missing times per employee is available in selected month
    - And missing time period ends within the 1st week
If employee has more than 1 missing period in selected month (i.e. there are several rows with missing times per employee in selected month)
- Example_C (please see attached pdf)
- Example_D (please see attached pdf)

Any help is more than appreciated.

Denis

topic HR use case: IntervalMatch & nested aggregations in App Development

HR use case: IntervalMatch & nested aggregations